
package com.compuvest.web;

// <editor-fold defaultstate="collapsed" desc="import">
import com.compuvest.webstatus.StatMap;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
// </editor-fold>

public class MySQL implements java.io.Serializable {

    public static final Logger log = LoggerFactory.getLogger(MySQL.class);

    private final static int VENDOR_CVS = 2; // compuvest

    private DataSource ds;

// <editor-fold defaultstate="collapsed" desc="MySQL">

    public MySQL() {

        try {
            Context initCtx = new InitialContext();
            ds = (DataSource)initCtx.lookup("jdbc/mysql");
        }
        catch(Exception ex) {
            log.error("init()", ex);
        }

        log.info("Init: version = " + getVersion());
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getVersion()">

    public final String getVersion() {

        try(
            Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement("SELECT version()");
            ResultSet rs = us.executeQuery())
        {
            if(rs.next()) {
                return rs.getString("version()");
            }
        }
        catch(SQLException e) {
            log.error("getVersion(): " + e);
        }

        return "Error";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="AdvertArray">

// <editor-fold defaultstate="collapsed" desc="getAdvertItemsFirstPage()">
// advert first page to Map

    public final static String SELECT_ADVERT_ITEMS_FIRSTPAGE =
        "SELECT i.item_id itemID,i.category_id categoryID,i.name itemName," +
        "i.hot_price sellPrice,i.accwareCatID accwareCode,c.parent parent " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "WHERE i.active='Y' AND im.firstpage=1 AND c.parent=? " +
        "LIMIT 32";

    final int[] catIDs = { 573, 446, 9, 30, 304, 13, 397, 625 };

    public HashMap<Integer,Item[]> getAdvertItemsFirstPage() {

        HashMap<Integer,Item[]> hashmap = new HashMap<>(catIDs.length);

        try(Connection conn = ds.getConnection()) {

            for(int categoryID : catIDs) {
                ArrayList<Item> itemList = new ArrayList<>(32);

                try(PreparedStatement us = conn.prepareStatement(SELECT_ADVERT_ITEMS_FIRSTPAGE)) {
                    us.setInt(1, categoryID);
                    try(ResultSet rs = us.executeQuery()) {
                        while(rs.next()) {
                            itemList.add(new Item(
                                rs.getInt("itemID"),
                                rs.getInt("categoryID"),
                                rs.getString("itemName"),
                                rs.getDouble("sellPrice"),
                                rs.getString("accwareCode"),
                                rs.getInt("parent") ));
                        }
                        hashmap.put(categoryID, (Item[])itemList.toArray(new Item[itemList.size()]));
                    }
                }
            }
        }
        catch(SQLException e) {
            log.error("getItemsAdvertFirstPage(): " + e);
        }

        return hashmap;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getAdvertItemsCategoryPage()">
// advert evry advert items Category to Map

    public final static String SELECT_ADVERT_ITEMS_CATEGORYPAGE =
        "SELECT i.item_id itemID,i.category_id categoryID,i.name itemName,i.hot_price sellPrice," +
        "i.accwareCatID accwareCode,c.parent parent " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "WHERE i.active='Y' AND im.categorypage=1 AND c.parent=? " +
        "LIMIT 32";

    public HashMap<Integer,Item[]> getAdvertItemsCategoryPage() {

        HashMap<Integer,Item[]> hashmap = new HashMap<>();

        try(Connection conn = ds.getConnection()) {

            // get category id
            ArrayList<Integer> categoryList = new ArrayList<>();
            PreparedStatement us = conn.prepareStatement("SELECT category_id FROM sklad.category WHERE publish='Y' AND parent=0");
            ResultSet rs = us.executeQuery();
            while(rs.next()) {
                categoryList.add(rs.getInt("category_id"));
            }
            rs.close();
            us.close();

            //
            for(int categoryID : categoryList) {
                ArrayList<Item> itemList = new ArrayList<>(32);

                us = conn.prepareStatement(SELECT_ADVERT_ITEMS_CATEGORYPAGE);
                us.setInt(1, categoryID);

                rs = us.executeQuery();
                while(rs.next()) {
                    // category
                    itemList.add(new Item(
                        rs.getInt("itemID"),
                        rs.getInt("categoryID"),
                        rs.getString("itemName"),
                        rs.getDouble("sellPrice"),
                        rs.getString("accwareCode"),
                        rs.getInt("parent")));
                }
                rs.close();
                us.close();

                hashmap.put(categoryID, (Item[])itemList.toArray(new Item[itemList.size()]));
            }
        }
        catch(SQLException e) {
            log.error("getAdvertItemsCategoryPage():" + e);
        }

        return hashmap;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getAdvertItemsPage()">
// advert every SubCategory Items to Map

    // fileld bestprice = advert subcategory page
    public final static String SELECT_ADVERT_ITEMSPAGE =
        "SELECT i.item_id itemID,i.category_id categoryID,i.name itemName," +
        "i.hot_price sellPrice,i.accwareCatID accwareCode,c.parent parent " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "WHERE i.active='Y' AND im.bestprice=1 AND c.category_id=? " +
        "LIMIT 10";

    public HashMap<Integer,Item[]> getAdvertItemsPage() {

        HashMap<Integer,Item[]> hashmap = new HashMap<>();

        try(Connection conn = ds.getConnection()) {

            // get category id
            ArrayList<Integer> categoryList = new ArrayList<>();
            PreparedStatement us = conn.prepareStatement("SELECT category_id FROM sklad.category WHERE publish='Y' AND parent=0");
            ResultSet rs = us.executeQuery();
            while(rs.next()) {
                categoryList.add(rs.getInt("category_id"));
            }
            rs.close();
            us.close();

            // get subcategory id
            ArrayList<Integer> subcategoryList = new ArrayList<>();
            for(int categoryID : categoryList) {
                us = conn.prepareStatement("SELECT category_id FROM sklad.category WHERE publish='Y' AND parent=?");
                us.setInt(1, categoryID);
                rs = us.executeQuery();
                while(rs.next()) {
                    subcategoryList.add(rs.getInt("category_id"));
                }
                rs.close();
                us.close();
            }

            // get items
            for(int subcategoryID : subcategoryList) {

                ArrayList<Item> itemSubList = new ArrayList<>(10);

                us = conn.prepareStatement(SELECT_ADVERT_ITEMSPAGE);
                us.setInt(1, subcategoryID);
                rs = us.executeQuery();
                // subcategory (items advert)
                while(rs.next()) {
                    itemSubList.add(new Item(
                        rs.getInt("itemID"),
                        rs.getInt("categoryID"),
                        rs.getString("itemName"),
                        rs.getDouble("sellPrice"),
                        rs.getString("accwareCode"),
                        rs.getInt("parent")));
                }
                rs.close();
                us.close();

                hashmap.put(subcategoryID, (Item[])itemSubList.toArray(new Item[itemSubList.size()]));
            }
        }
        catch(SQLException e) {
            log.error("getAdvertItemsPage(): " + e);
        }

        return hashmap;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="selectMenuString()">

    public String selectMenuString() {

        StringBuilder menu = new StringBuilder(20000);

        try(Connection conn = ds.getConnection()) {

            menu.append("<div style='margin:20px 0 5px 2px;'>");
            menu.append("<table width='100%' cellspacing=0 cellpadding=0>");
            menu.append("<tr><td id=menublock><ul id=menu>\n");

            PreparedStatement us1 = null;
            ResultSet rs1 = null;

            PreparedStatement us = conn.prepareStatement("SELECT category_id,name FROM sklad.category WHERE publish='Y' AND parent=0 ORDER BY sort,name");
            ResultSet rs = us.executeQuery();
            while(rs.next()) {
                menu.append("<li/><a href='?cid=").append(rs.getInt("category_id")).append("'>");
                menu.append(rs.getString("name")).append("</a>\n");
                menu.append("<ul>");

                us1 = conn.prepareStatement("SELECT category_id,name FROM sklad.category WHERE publish='Y' AND parent=? ORDER BY sort,name");
                us1.setInt(1, rs.getInt("category_id"));
                rs1 = us1.executeQuery();
                while(rs1.next()) {
                    menu.append("<li/><a href='?cid=");
                    menu.append(rs.getInt("category_id")).append("&sid=").append(rs1.getInt("category_id")).append("'>");
                    menu.append(rs1.getString("name")).append("</a>");
                }
                rs1.close();
                us1.close();

                menu.append("</ul>\n");
            }
            rs.close();
            us.close();

            menu.append("</ul></td></tr></table>");
            menu.append("</div>");
        }
        catch(SQLException e) {
            log.error("selectMenu(): " + e);
        }

        return menu.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="selectCategoryAll()">

    public final static String SELECT_CATEGORY_ALL =
        "SELECT category_id,name FROM sklad.category WHERE publish='Y' AND parent=0 ORDER BY sort,name";
    public final static String SELECT_SUBCATEGORY_ALL =
        "SELECT category_id,parent,name FROM sklad.category WHERE publish='Y' AND parent=? ORDER BY sort,name";

    public int selectCategoryAll() {

        Map<Integer,String> cMap = new HashMap<>();
        Map<Integer,ArrayList<Category>> sMap = new HashMap<>();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us1 = null;
            ResultSet rs1 = null;

            PreparedStatement us = conn.prepareStatement(SELECT_CATEGORY_ALL);
            ResultSet rs = us.executeQuery();
            for(int c = 0; rs.next(); c++) {
                if(c == 0) { cMap.put(0, rs.getString("name")); }

                cMap.put(rs.getInt("category_id"), rs.getString("name"));

                ArrayList<Category> subcatList = new ArrayList<>(50);

                us1 = conn.prepareStatement(SELECT_SUBCATEGORY_ALL);
                us1.setInt(1, rs.getInt("category_id"));
                rs1 = us1.executeQuery();
                for(int s = 0; rs1.next(); s++) {
                    Category subcategory = new Category(rs1.getInt("category_id"),
                                                        rs1.getInt("parent"),
                                                        rs1.getString("name"),
                                                        0);
                    if(s == 0) {
                        ArrayList<Category> subcatTemp = new ArrayList<>();
                        subcatTemp.add(subcategory);
                        sMap.put(0, subcatTemp);
                    }
                    subcatList.add(subcategory);
                }
                rs1.close();
                us1.close();

                sMap.put(rs.getInt("category_id"), subcatList);
            }
            rs.close();
            us.close();

            AdvertArray.categoryMap = cMap;
            AdvertArray.subcatMap = sMap;
        }
        catch(SQLException e) {
            log.error("selectCategoryAll(): " + e);
        }

        return 0;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="selectCountryString()">

    public final static String SELECT_COUNTRY = "SELECT countryID,description FROM sklad.uspszone ORDER BY description";

    public String selectCountryString() {

        StringBuilder cntry = new StringBuilder(6000);

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(SELECT_COUNTRY);
            ResultSet rs = us.executeQuery();
            while(rs.next()) {
                cntry.append("<option value='").append(rs.getString("countryID")).append("'/>").append(rs.getString("description"));
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.error("selectCountryString(): " + e);
        }

        return cntry.toString();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="selectStateString()">

    public final static String SELECT_STATE = "SELECT state_code,description FROM sklad.state_code";

    public String selectStateString() {

        StringBuilder st = new StringBuilder(3000);

        try(
            Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement(SELECT_STATE);
            ResultSet rs = us.executeQuery())
        {
            while(rs.next()) {
                st.append("<option value='").append(rs.getString("state_code")).append("'/>").append(rs.getString("description"));
            }
        }
        catch(SQLException e) {
            log.error("selectStateString(): " + e);
        }

        return st.toString();
    }
// </editor-fold>

// </editor-fold>


// <editor-fold defaultstate="collapsed" desc="setDropPrice()">

    public final static String INSERT_DROPPRICE =
        "INSERT LOW_PRIORITY sklad.dropPrice SET date:=NOW(),item_id:=?,name:=?,phone:=?,email:=?,notation:=?";

    public void setDropPrice(int itemID, String name, String phone, String email, String notation) {

        try(
            Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement(INSERT_DROPPRICE))
        {
            us.setInt(1, itemID);
            us.setString(2, name);
            us.setString(3, phone);
            us.setString(4, email);
            us.setString(5, notation);

            us.executeUpdate();
        }
        catch(SQLException e) {
            log.error("setDropPrice(): " + e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getTrackingOrder()">

    public final static String tracking_sql = 
        "SELECT o.order_id order_id,o.orderNo orderNo,DATE_FORMAT(o.order_date,'%b %e, %Y %l%p') orderDate," +
        "o.shipping_price shipping_price,o.tax_price tax_price,o.amount amount,o.cod cod," +
        "o.shipping_code shipping_code,IF(LENGTH(oh.order_cp) > 0,true,false) haveHistory ";

    public ArrayList<Order> getTrackingOrder(String email, String password, String orderNumber, String phone) {

        StringBuilder query = new StringBuilder(tracking_sql);

        if(!"".equals(email) && !"".equals(password)) {
            query.append("FROM sklad.orders o LEFT JOIN sklad.orderHistory oh USING(order_id) LEFT JOIN sklad.customer c USING(customer_id) ");
            query.append("WHERE c.email='").append(email).append("' AND c.password='").append(password).append("' ORDER BY o.order_date DESC");
        }
        else if(!"".equals(orderNumber)) {
            query.append("FROM sklad.orders o LEFT JOIN sklad.orderHistory oh USING(order_id) ");
            query.append("WHERE o.orderNo='").append(orderNumber).append("' ");
        }
        else if(!"".equals(phone)) {
            query.append("FROM sklad.orders o LEFT JOIN sklad.orderHistory oh USING(order_id) LEFT JOIN sklad.customer c USING(customer_id) ");
            query.append("WHERE c.billingPhone='").append(phone).append("' ORDER BY o.order_date DESC");
        }
        else {
            return new ArrayList<>();
        }

        ArrayList<Order> orderList = new ArrayList<>();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us2 = null;
            ResultSet rs2 = null;

            PreparedStatement us = conn.prepareStatement(query.toString());
            ResultSet rs = us.executeQuery();
            while(rs.next()) {
                Order order = new Order();

                order.setOrderID(rs.getInt("order_id"));
                order.setOrderNo(rs.getString("orderNo"));
                order.setOrderDate(rs.getString("orderDate"));
                order.setShippingCost(rs.getDouble("shipping_price"));
                order.setTaxCost(rs.getDouble("tax_price"));
                order.setGrandTotal(rs.getDouble("amount"));
                order.setCOD(rs.getString("cod"));
                order.setShippingDesc(rs.getString("shipping_code"));
                order.haveHistory(rs.getBoolean("haveHistory"));

                us2 = conn.prepareStatement("SELECT trackingNumber trackingNo,DATE_FORMAT(shippedDate,'%b %e, %Y %l%p') shippDate FROM sklad.tracking WHERE order_id=? ORDER BY shippedDate DESC");
                us2.setInt(1, rs.getInt("order_id"));

                rs2 = us2.executeQuery();
                while(rs2.next()) {
                    order.addTracking(new Tracking(rs2.getString("trackingNo"), rs2.getString("shippDate")));
                }
                rs2.close();
                us2.close();

                orderList.add(order);
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.error("getTrackingOrder(): " + e);
        }

        return orderList;
    }
// </editor-fold>
    
// <editor-fold defaultstate="collapsed" desc="getOrder()">

    public String getOrder(int orderID) {

        String body = "";

        try(
            Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement("SELECT order_cp FROM sklad.orderHistory WHERE order_id=?"))
        {
            us.setInt(1, orderID);
            try (ResultSet rs = us.executeQuery())
            {
                if(rs.next()) {
                    body = rs.getString("order_cp");
                }
            }
        }
        catch(SQLException e) {
            log.error("getOrder(): " + e);
        }

        return body;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getOrders()">

    public static final String GET_ORDERS =
        "SELECT order_id,orderNo,DATE_FORMAT(order_date,'%b %e, %Y %l%p') orderDate,shipping_price,tax_price," +
        "amount,cod,shipping_code,IF(LENGTH(order_cp) > 0,true,false) haveHistory " +
        "FROM sklad.orders LEFT JOIN sklad.orderHistory USING(order_id) " +
        "WHERE customer_id=? ORDER BY order_date DESC";

    public List<Order> getOrders(int customerID) {

        List<Order> orderList = new ArrayList<>();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us2 = null;
            ResultSet rs2 = null;

            PreparedStatement us = conn.prepareStatement(GET_ORDERS);
            us.setInt(1, customerID);
            ResultSet rs = us.executeQuery();
            while(rs.next()) {
                Order order = new Order();

                order.setOrderID(rs.getInt("order_id"));
                order.setOrderNo(rs.getString("orderNo"));
                order.setOrderDate(rs.getString("orderDate"));
                order.setShippingCost(rs.getDouble("shipping_price"));
                order.setTaxCost(rs.getDouble("tax_price"));
                order.setGrandTotal(rs.getDouble("amount"));
                order.setCOD(rs.getString("cod"));
                order.setShippingDesc(rs.getString("shipping_code"));
                order.haveHistory(rs.getBoolean("haveHistory"));

                us2 = conn.prepareStatement(
                    "SELECT trackingNumber trackingNo,DATE_FORMAT(shippedDate,'%b %e, %Y %l%p') shippDate FROM sklad.tracking WHERE order_id=? ORDER BY shippedDate DESC");
                us2.setInt(1, rs.getInt("order_id"));

                rs2 = us2.executeQuery();
                while(rs2.next()) {
                    order.addTracking(new Tracking(rs2.getString("trackingNo"), rs2.getString("shippDate")));
                }
                rs2.close();
                us2.close();

                orderList.add(order);
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.error("getOrders(): " + e);
        }

        return orderList;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="saveCustomerLog()">

    public int saveCustomerLog(String host) {

        try(
            Connection conn = ds.getConnection();
            CallableStatement cs = conn.prepareCall("{ call sklad.setDomainStat(?,1,0,0) }"))
        {
            cs.setInt(1, 1); //1 = compuvest

            cs.executeUpdate();
        }
        catch(SQLException e) {
            log.error("saveCustomerLog(): " + e);
        }

        return 0;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItem()">

    public Items getItem(int itemID, String accwareCode, String host) {

        long startEvent = System.currentTimeMillis();

        String expr = accwareCode == null ? "i.item_id=" + itemID : "i.accwareCatID='" + accwareCode + "'";

        Items items = new Items();

        int categoryID;
        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(new StringBuilder(2500).
                append("SELECT HIGH_PRIORITY i.item_id itemID,i.category_id categoryID,i.name itemName,IFNULL(itd.json,'') json,").
                append("i.hot_price sellPrice,i.res_price1 resPrice1,i.res_price2 resPrice2,i.res_quantity2 resQuantity2,").
                append("i.res_price3 resPrice3,i.res_quantity3 resQuantity3,i.stock_quantity-i.reserv_quantity quantity,").
                append("IF(i.stock='Y',true,false) stock,i.weight weight,RIGHT(i.accwareCatID,12) accwareCode,").
                append("i.mfr_name mfrName,i.mfr_number mfrNumber,i.mfr_link mfrLink,cs.description conditions,").
                append("ps.description package,i.package_include packageInclude,i.comments comments,").
                append("g.name catName,c.name subcatName,g.category_id catID,c.category_id subcatID,").

                append("(im.special | im.closeout<<0x1 | im.commingsoon<<0x2 | im.bestprice<<0x3) bannerMask,i.warrantyMask warrantyMask,").
                append("i.warrantyPrice1 warrantyPrice1,i.warrantyPrice2 warrantyPrice2,i.warrantyPrice3 warrantyPrice3,").
                append("CONCAT(").
                append("IF(IFNULL(p.pref2,'')='','',CONCAT('AND p.pref2=',QUOTE(p.pref2),' ')),").
                append("IF(IFNULL(p.pref3,'')='','',CONCAT('AND p.pref3=',QUOTE(p.pref3),' ')),").
                append("IF(IFNULL(p.pref4,'')='','',CONCAT('AND p.pref4=',QUOTE(p.pref4),' ')),").
                append("IF(IFNULL(p.pref5,'')='','',CONCAT('AND p.pref5=',QUOTE(p.pref5),' '))").
                append(") search,c.name categoryName,c.parent parent,g.name subcatName ").
                append("FROM sklad.items i ").
                append("LEFT JOIN sklad.itemsDesc itd USING(item_id) ").
                append("LEFT JOIN sklad.itemsMask im USING(item_id) ").
                append("LEFT JOIN sklad.category c USING(category_id) ").
                append("LEFT JOIN sklad.category g ON c.parent=g.category_id ").
                append("LEFT JOIN sklad.preference p ON i.item_id=p.item_id ").
                append("LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id ").
                append("LEFT JOIN sklad.packages ps ON RIGHT(RIGHT(i.accwareCatID,2),1)=ps.id ").
                append("WHERE i.active='Y' AND ").append(expr).toString());

            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                categoryID = rs.getInt("categoryID");

                Item item = new Item();

                item.setItemID(rs.getInt("itemID"));
                item.setCategoryID(categoryID);
                item.setItemName(rs.getString("itemName"));
                item.setDescription(rs.getString("json"));
                item.setPrice(rs.getDouble("sellPrice"));
                item.setResPrice1(rs.getDouble("resPrice1"));
                item.setResPrice2(rs.getDouble("resPrice2"));
                item.setResQuantity2(rs.getInt("resQuantity2"));
                item.setResPrice3(rs.getDouble("resPrice3"));
                item.setResQuantity3(rs.getInt("resQuantity3"));
                item.setQuantity(rs.getInt("quantity"));
                item.setStock(rs.getBoolean("stock"));
                item.setWeight(rs.getDouble("weight"));
                item.setAccwareCode(rs.getString("accwareCode"));
                item.setMfrName(rs.getString("mfrName"));
                item.setMfrNumber(rs.getString("mfrNumber"));
                item.setMfrLink(rs.getString("mfrLink"));
                item.setCondition(rs.getString("conditions"));
                item.setPackage(rs.getString("package"));
                item.setPackageInclude(rs.getString("packageInclude"));
                item.setBannerMask(rs.getInt("bannerMask"));
                item.setWarrantyMask(rs.getInt("warrantyMask"));
                item.setWarrantyPrice1(rs.getDouble("warrantyPrice1"));
                item.setWarrantyPrice2(rs.getDouble("warrantyPrice2"));
                item.setWarrantyPrice3(rs.getDouble("warrantyPrice3"));
                item.setComments(rs.getString("comments"));

                item.setCategoryName(rs.getString("categoryName"));
                item.setCategoryParent(rs.getInt("parent"));
                item.setSubcategoryName(rs.getString("subcatName"));

//                search = rs.getString("search");

                items.setCategoryID(rs.getInt("catID"));
                items.setSubcategoryID(rs.getInt("subcatID"));

                items.setCategoryName(rs.getString("catName"));
                items.setSubcategoryName(rs.getString("subcatName"));

                items.addItem(item);
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.error("getItem(): " + e);
        }
        items.setEventTime((int)(System.currentTimeMillis() - startEvent));

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemDescription()">

    public String getItemDescription(int itemID) {

        if(itemID == 0) { return null; }

        String description = "";

        try(
            Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement(
                "SELECT IFNULL(itd.json,'') json " +
                "FROM sklad.items i " +
                "LEFT JOIN sklad.itemsDesc itd USING(item_id) " +
                "WHERE i.active='Y' AND i.item_id=?"))
        {
            us.setInt(1, itemID);
            try(ResultSet rs = us.executeQuery())
            {
                if(rs.next()) {
    //                description = rs.getString("json");
                }
            }
        }
        catch(SQLException e) {
            log.error("getItemDescription(): " + e);
        }

        return description;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemForBasket()">

    public static final String GET_ITEM_FOR_BASKET =
        "SELECT HIGH_PRIORITY item_id,category_id,name,hot_price,res_price1,res_price2,res_quantity2,res_price3," +
        "res_quantity3,(stock_quantity-reserv_quantity) quantity,IF(stock='Y',true,false) stock,weight," +
        "RIGHT(accwareCatID,12) accwareCode,mfr_number,cs.description conditions," +
        "warrantyMask,warrantyPrice1,warrantyPrice2,warrantyPrice3 " +
        "FROM sklad.items i LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(accwareCatID,2),1)=cs.id " +
        "WHERE active='Y' AND item_id=?";

    public Item getItemForBasket(int itemID) {

        Item item = new Item();

        try(
            Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement(GET_ITEM_FOR_BASKET))
        {
            us.setInt(1, itemID);

            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                item.setItemID(rs.getInt("item_id"));
                item.setCategoryID(rs.getInt("category_id"));
                item.setItemName(rs.getString("name"));
                item.setPrice(rs.getDouble("hot_price"));
                item.setResPrice1(rs.getDouble("res_price1"));
                item.setResPrice2(rs.getDouble("res_price2"));
                item.setResQuantity2(rs.getInt("res_quantity2"));
                item.setResPrice3(rs.getDouble("res_price3"));
                item.setResQuantity3(rs.getInt("res_quantity3"));
                item.setQuantity(rs.getInt("quantity"));
                item.setStock(rs.getBoolean("stock"));
                item.setWeight(rs.getDouble("weight"));
                item.setAccwareCode(rs.getString("accwareCode"));
                item.setMfrNumber(rs.getString("mfr_number"));
                item.setCondition(rs.getString("conditions"));
                item.setWarrantyMask(rs.getInt("warrantyMask"));
                item.setWarrantyPrice1(rs.getDouble("warrantyPrice1"));
                item.setWarrantyPrice2(rs.getDouble("warrantyPrice2"));
                item.setWarrantyPrice3(rs.getDouble("warrantyPrice3"));
            }
            rs.close();
        }
        catch(SQLException e) {
            log.error("getItemForBasket(): " + e);
        }

        return item;
    }
// </editor-fold>


// <editor-fold defaultstate="collapsed" desc="getItemsNewArrival()">

    public final static String NEWARRIVAL_CATEGORY =
        "SELECT count(*) itemsCount,g.category_id category_id,g.name name " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "WHERE i.active='Y' AND i.vendor_id=2 AND i.stock_quantity > 0 " +
        "AND DATE_SUB(CURDATE(), INTERVAL 5 DAY) <= i.createTime AND g.category_id IS NOT NULL " +
        "GROUP BY g.category_id ORDER BY g.sort,g.name";
    public final static String NEWARRIVAL_SUBCATEGORY =
        "SELECT count(*) itemsCount,c.category_id category_id,c.name scatName,g.name catName " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "WHERE i.active='Y' AND i.vendor_id=2 AND i.stock_quantity > 0 " +
        "AND DATE_SUB(CURDATE(), INTERVAL 5 DAY) <= i.createTime AND c.parent=? " +
        "GROUP BY c.category_id ORDER BY c.sort,c.name";
    public final static String NEWARRIVAL_PREFERENCE =
        "SELECT i.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.preference p USING(item_id) " +
        "LEFT JOIN sklad.conditions cs ON " +
        "LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
        "WHERE i.active='Y' AND i.vendor_id=2 AND i.stock_quantity > 0 " +
        "AND DATE_SUB(CURDATE(), INTERVAL 5 DAY) <= i.createTime AND i.category_id=? ";
    public final static String NEWARRIVAL_GET_ITEMS =
        "SELECT SQL_CALC_FOUND_ROWS i.item_id itemID,i.name itemName,i.hot_price sellPrice," +
        "i.res_price1 resPrice1,i.res_price2 resPrice2,i.res_quantity2 resQuantity2,i.res_price3 resPrice3," +
        "i.res_quantity3 resQuantity3," +
        "i.stock_quantity-i.reserv_quantity quantity,IF(i.stock='Y',true,false) stock," +
        "RIGHT(i.accwareCatID,12) accwareCode,i.mfr_name mfrName,i.mfr_number mfrNumber," +
        "g.name catName,c.name subcatName,g.category_id catID,c.category_id subcatID," +
        "c.searchName1 sn1,c.searchName2 sn2,c.searchName3 sn3,c.searchName4 sn4,c.searchName5 sn5," +
        "(im.special | im.closeout<<0x1 | im.commingsoon<<0x2 | im.bestprice<<0x3) bannerMask," +
        "cs.description conditions " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "LEFT JOIN sklad.preference p ON i.item_id=p.item_id " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
        "WHERE i.active='Y' AND i.vendor_id=2 AND i.stock_quantity > 0 " +
        "AND DATE_SUB(CURDATE(), INTERVAL 5 DAY) <= i.createTime ";

    public Items getItemsNewArrival(int categoryID, int subcatID, int sortID, Map<Integer,String> popData, int lowPrice, int hiPrice, int pageNo) {

        long startEvent = System.currentTimeMillis();

        Items items = new Items();

        String catstr  = categoryID == 0 ? "" : " AND c.parent=" + categoryID  + " ";
        String scatstr = subcatID == 0   ? "" : " AND c.category_id=" + subcatID  + " ";

        //sort
        String sort;
        switch(sortID) {
            case  1 : sort = " ORDER BY i.hot_price ASC  "; break;
            case  2 : sort = " ORDER BY i.hot_price DESC "; break;
            default : sort = "";
        }

        // Preference
        StringBuilder strSel = new StringBuilder(200);
        if(popData != null) {
            for(int key : popData.keySet()) {
                switch(key) {
                    case  0 : strSel.append("AND i.mfr_name='").append(popData.get(0)).append("' "); break;
                    case  1 : strSel.append("AND cs.description='").append(popData.get(1)).append("' "); break;
                    default : strSel.append("AND p.pref").append(key-1).append("='").append(popData.get(key)).append("' ");
                }
            }
        }
        strSel.append((lowPrice != 0 && hiPrice != 0) ? "AND i.hot_price BETWEEN " + lowPrice + " AND " + hiPrice : "");

        Preference mfrName = new Preference();
        Preference conds   = new Preference();
        Preference block1  = new Preference();
        Preference block2  = new Preference();
        Preference block3  = new Preference();
        Preference block4  = new Preference();
        Preference block5  = new Preference();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = null;
            ResultSet rs = null;
//------------------------------
            // make category
            if(categoryID == 0) {
                us = conn.prepareStatement(NEWARRIVAL_CATEGORY);
                rs = us.executeQuery();
                while(rs.next()) {
                    items.addCategory(new Category(
                        rs.getInt("category_id"),
                        0,
                        rs.getString("name"),
                        rs.getInt("itemsCount") ));
                }
                rs.close();
                us.close();
            }
//------------------------------
            // make subcategory
            else if(categoryID != 0 && subcatID == 0) {
                us = conn.prepareStatement(NEWARRIVAL_SUBCATEGORY);
                us.setInt(1, categoryID);
                rs = us.executeQuery();
                for(int i = 0; rs.next(); i++) {
                    if(i == 0) {
                        items.setCategoryName(rs.getString("catName"));
                    }
                    items.addSubcategory(new Category(
                        rs.getInt("category_id"),
                        0,
                        rs.getString("scatName"),
                        rs.getInt("itemsCount") ));
                }
                rs.close();
                us.close();
            }

//------------------------------
            // get items and fill preference
            else if(categoryID != 0 && subcatID != 0) {
                us = conn.prepareStatement(NEWARRIVAL_PREFERENCE + strSel);
                us.setInt(1, subcatID);
                rs = us.executeQuery();
                while(rs.next()) {
                    mfrName.addContent(rs.getString("mfr_name"));
                    conds.addContent(rs.getString("conds"));
                    block1.addContent(rs.getString("pref1"));
                    block2.addContent(rs.getString("pref2"));
                    block3.addContent(rs.getString("pref3"));
                    block4.addContent(rs.getString("pref4"));
                    block5.addContent(rs.getString("pref5"));
                }
                rs.close();
                us.close();
            }
//------------------------------
            // get items
            us = conn.prepareStatement(NEWARRIVAL_GET_ITEMS + strSel + catstr + scatstr + sort + " LIMIT ?,?");
            us.setInt(1, pageNo * Items.LINE_ON_PAGE);
            us.setInt(2, Items.LINE_ON_PAGE);

            rs = us.executeQuery();
            boolean onetime = true;
            while(rs.next()) {
                if(onetime) {
                    onetime = !onetime;

                    mfrName.setTitle("Manufacturer"); items.addPreference(mfrName);
                    conds.setTitle("Condition"); items.addPreference(conds);
                    block1.setTitle(rs.getString("sn1")); items.addPreference(block1);
                    block2.setTitle(rs.getString("sn2")); items.addPreference(block2);
                    block3.setTitle(rs.getString("sn3")); items.addPreference(block3);
                    block4.setTitle(rs.getString("sn4")); items.addPreference(block4);
                    block5.setTitle(rs.getString("sn5")); items.addPreference(block5);

                    items.setCategoryID(rs.getInt("catID"));
                    items.setSubcategoryID(rs.getInt("subcatID"));

                    items.setCategoryName(rs.getString("catName"));
                    items.setSubcategoryName(rs.getString("subcatName"));
                }
                Item item = new Item();

                item.setItemID(rs.getInt("itemID"));
                item.setItemName(rs.getString("itemName"));
                item.setPrice(rs.getDouble("sellPrice"));
                item.setResPrice1(rs.getDouble("resPrice1"));
                item.setResPrice2(rs.getDouble("resPrice2"));
                item.setResQuantity2(rs.getInt("resQuantity2"));
                item.setResPrice3(rs.getDouble("resPrice3"));
                item.setResQuantity3(rs.getInt("resQuantity3"));
                item.setQuantity(rs.getInt("quantity"));
                item.setStock(rs.getBoolean("stock"));
                item.setAccwareCode(rs.getString("accwareCode"));
                item.setMfrName(rs.getString("mfrName"));
                item.setMfrNumber(rs.getString("mfrNumber"));
                item.setBannerMask(rs.getInt("bannerMask"));
                item.setCondition(rs.getString("conditions"));

                items.addItem(item);
            }
            rs.close();
            us.close();

            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() itemsCount");
            rs = us.executeQuery();
            if(rs.next()) {
                items.setItemsCount(rs.getInt("itemsCount"));
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.error("getItemsNewArrival(): " + e);
        }
        items.setEventTime((int)(System.currentTimeMillis() - startEvent));

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsCloseOut()">

    public final static String CLOSEOUT_CATEGORY =
        "SELECT count(*) itemsCount,g.category_id category_id,g.name name " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "WHERE i.active='Y' AND im.page_closeout=1 AND g.category_id IS NOT NULL " +
        "GROUP BY g.category_id ORDER BY g.sort,g.name";
    public final static String CLOSEOUT_SUBCATEGORY =
        "SELECT count(*) itemsCount,c.category_id category_id,c.name scatName,g.name catName " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "WHERE i.active='Y' AND im.page_closeout=1 AND c.parent=? " +
        "GROUP BY c.category_id ORDER BY c.sort,c.name";
    public final static String CLOSEOUT_PREFERENCE =
        "SELECT i.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.preference p USING(item_id) " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
        "WHERE i.active='Y' AND im.page_closeout=1 AND i.category_id=? ";
    public final static String CLOSEOUT_GET_ITEMS =
        "SELECT SQL_CALC_FOUND_ROWS i.item_id itemID,i.name itemName,i.hot_price sellPrice," +
        "i.res_price1 resPrice1,i.res_price2 resPrice2,i.res_quantity2 resQuantity2,i.res_price3 resPrice3," +
        "i.res_quantity3 resQuantity3," +
        "i.stock_quantity-i.reserv_quantity quantity,IF(i.stock='Y',true,false) stock," +
        "RIGHT(i.accwareCatID,12) accwareCode,i.mfr_name mfrName,i.mfr_number mfrNumber," +
        "g.name catName,c.name subcatName,g.category_id catID,c.category_id subcatID," +
        "c.searchName1 sn1,c.searchName2 sn2,c.searchName3 sn3,c.searchName4 sn4,c.searchName5 sn5," +
        "(im.special | im.closeout<<0x1 | im.commingsoon<<0x2 | im.bestprice<<0x3) bannerMask," +
        "cs.description conditions " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "LEFT JOIN sklad.preference p ON i.item_id=p.item_id " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
        "WHERE i.active='Y' AND im.page_closeout=1 ";

    public Items getItemsCloseOut(int categoryID, int subcatID, int sortID, Map<Integer,String> popData, int lowPrice, int hiPrice, int pageNo) {

        long startEvent = System.currentTimeMillis();

        Items items = new Items();

        String catstr  = categoryID == 0 ? "" : " AND c.parent=" + categoryID  + " ";
        String scatstr = subcatID == 0   ? "" : " AND c.category_id=" + subcatID  + " ";

        //sort
        String sort;
        switch(sortID) {
            case  1 : sort = " ORDER BY i.hot_price ASC  "; break;
            case  2 : sort = " ORDER BY i.hot_price DESC "; break;
            default : sort = "";
        }

        // Preference
        StringBuilder strSel = new StringBuilder(200);
        if(popData != null) {
            for(int key : popData.keySet()) {
                switch(key) {
                    case  0 : strSel.append("AND i.mfr_name='").append(popData.get(0)).append("' "); break;
                    case  1 : strSel.append("AND cs.description='").append(popData.get(1)).append("' "); break;
                    default : strSel.append("AND p.pref").append(key-1).append("='").append(popData.get(key)).append("' ");
                }
            }
        }
        strSel.append((lowPrice != 0 && hiPrice != 0) ? "AND i.hot_price BETWEEN " + lowPrice + " AND " + hiPrice : "");

        Preference mfrName = new Preference();
        Preference conds   = new Preference();
        Preference block1  = new Preference();
        Preference block2  = new Preference();
        Preference block3  = new Preference();
        Preference block4  = new Preference();
        Preference block5  = new Preference();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = null;
            ResultSet rs = null;
//------------------------------
            // make category
            if(categoryID == 0) {
                us = conn.prepareStatement(CLOSEOUT_CATEGORY);
                rs = us.executeQuery();
                while(rs.next()) {
                    items.addCategory(new Category(
                        rs.getInt("category_id"),
                        0,
                        rs.getString("name"),
                        rs.getInt("itemsCount") ));
                }
                rs.close();
                us.close();
            }
//------------------------------
            // make subcategory
            else if(categoryID != 0 && subcatID == 0) {
                us = conn.prepareStatement(CLOSEOUT_SUBCATEGORY);
                us.setInt(1, categoryID);
                rs = us.executeQuery();
                for(int i = 0; rs.next(); i++) {
                    if(i == 0) {
                        items.setCategoryName(rs.getString("catName"));
                    }
                    items.addSubcategory(new Category(
                        rs.getInt("category_id"),
                        0,
                        rs.getString("scatName"),
                        rs.getInt("itemsCount") ));
                }
                rs.close();
                us.close();
            }

//------------------------------
            // get items and fill preference
            else if(categoryID != 0 && subcatID != 0) {
                us = conn.prepareStatement(CLOSEOUT_PREFERENCE + strSel);
                us.setInt(1, subcatID);
                rs = us.executeQuery();
                while(rs.next()) {
                    mfrName.addContent(rs.getString("mfr_name"));
                    conds.addContent(rs.getString("conds"));
                    block1.addContent(rs.getString("pref1"));
                    block2.addContent(rs.getString("pref2"));
                    block3.addContent(rs.getString("pref3"));
                    block4.addContent(rs.getString("pref4"));
                    block5.addContent(rs.getString("pref5"));
                }
                rs.close();
                us.close();
            }

//------------------------------
            // get items
            us = conn.prepareStatement(CLOSEOUT_GET_ITEMS + strSel + catstr + scatstr + sort + " LIMIT ?,?");
            us.setInt(1, pageNo * Items.LINE_ON_PAGE);
            us.setInt(2, Items.LINE_ON_PAGE);
            rs = us.executeQuery();
            boolean onetime = true;
            while(rs.next()) {
                if(onetime) {
                    onetime = !onetime;

                    mfrName.setTitle("Manufacturer"); items.addPreference(mfrName);
                    conds.setTitle("Condition"); items.addPreference(conds);
                    block1.setTitle(rs.getString("sn1")); items.addPreference(block1);
                    block2.setTitle(rs.getString("sn2")); items.addPreference(block2);
                    block3.setTitle(rs.getString("sn3")); items.addPreference(block3);
                    block4.setTitle(rs.getString("sn4")); items.addPreference(block4);
                    block5.setTitle(rs.getString("sn5")); items.addPreference(block5);

                    items.setCategoryID(rs.getInt("catID"));
                    items.setSubcategoryID(rs.getInt("subcatID"));

                    items.setCategoryName(rs.getString("catName"));
                    items.setSubcategoryName(rs.getString("subcatName"));
                }
                Item item = new Item();

                item.setItemID(rs.getInt("itemID"));
                item.setItemName(rs.getString("itemName"));
                item.setPrice(rs.getDouble("sellPrice"));
                item.setResPrice1(rs.getDouble("resPrice1"));
                item.setResPrice2(rs.getDouble("resPrice2"));
                item.setResQuantity2(rs.getInt("resQuantity2"));
                item.setResPrice3(rs.getDouble("resPrice3"));
                item.setResQuantity3(rs.getInt("resQuantity3"));
                item.setQuantity(rs.getInt("quantity"));
                item.setStock(rs.getBoolean("stock"));
                item.setAccwareCode(rs.getString("accwareCode"));
                item.setMfrName(rs.getString("mfrName"));
                item.setMfrNumber(rs.getString("mfrNumber"));
                item.setBannerMask(rs.getInt("bannerMask"));
                item.setCondition(rs.getString("conditions"));

                items.addItem(item);
            }
            rs.close();
            us.close();

            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() itemsCount");
            rs = us.executeQuery();
            if(rs.next()) {
                items.setItemsCount(rs.getInt("itemsCount"));
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.error("getItemsCloseOut(): " + e);
        }
        items.setEventTime((int)(System.currentTimeMillis() - startEvent));

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsHotDeals()">
// Hot Deals
// use special=1

    public final static String HOTDEALS_CATEGORY =
        "SELECT count(*) itemsCount,g.category_id category_id,g.name name " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "WHERE i.active='Y' AND im.page_hotdeals=1 AND g.category_id IS NOT NULL " +
        "GROUP BY g.category_id ORDER BY g.sort,g.name";
    public final static String HOTDEALS_SUBCATEGORY =
        "SELECT count(*) itemsCount,c.category_id category_id,c.name scatName,g.name catName " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "WHERE i.active='Y' AND im.page_hotdeals=1 AND c.parent=? " +
        "GROUP BY c.category_id ORDER BY c.sort,c.name";
    public final static String HOTDEALS_PREFERENCE =
        "SELECT i.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.preference p USING(item_id) " +
        "LEFT JOIN sklad.conditions cs ON " +
        "LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
        "WHERE i.active='Y' AND im.page_hotdeals=1 AND i.category_id=? ";
    public final static String HOTDEALS_GET_ITEMS =
        "SELECT SQL_CALC_FOUND_ROWS i.item_id itemID,i.name itemName,i.hot_price sellPrice," +
        "i.res_price1 resPrice1,i.res_price2 resPrice2,i.res_quantity2 resQuantity2,i.res_price3 resPrice3," +
        "i.res_quantity3 resQuantity3," +
        "i.stock_quantity-i.reserv_quantity quantity,IF(i.stock='Y',true,false) stock," +
        "RIGHT(i.accwareCatID,12) accwareCode,i.mfr_name mfrName,i.mfr_number mfrNumber," +
        "g.name catName,c.name subcatName,g.category_id catID,c.category_id subcatID," +
        "c.searchName1 sn1,c.searchName2 sn2,c.searchName3 sn3,c.searchName4 sn4,c.searchName5 sn5," +
        "(im.special | im.closeout<<0x1 | im.commingsoon<<0x2 | im.bestprice<<0x3) bannerMask," +
        "cs.description conditions " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "LEFT JOIN sklad.preference p ON i.item_id=p.item_id " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
        "WHERE i.active='Y' AND im.page_hotdeals=1 ";

    public Items getItemsHotDeals(int categoryID, int subcatID, int sortID, Map<Integer,String> popData, int lowPrice, int hiPrice, int pageNo) {

        long startEvent = System.currentTimeMillis();

        Items items = new Items();

        String catstr  = categoryID == 0 ? "" : " AND c.parent=" + categoryID  + " ";
        String scatstr = subcatID == 0   ? "" : " AND c.category_id=" + subcatID  + " ";

        //sort
        String sort;
            switch(sortID) {
            case  1 : sort = " ORDER BY i.hot_price ASC  "; break;
            case  2 : sort = " ORDER BY i.hot_price DESC "; break;
            default : sort = "";
        }

        // Preference
        StringBuilder strSel = new StringBuilder(200);
        if(popData != null) {
            for(int key : popData.keySet()) {
                switch(key) {
                    case  0 : strSel.append("AND i.mfr_name='").append(popData.get(0)).append("' "); break;
                    case  1 : strSel.append("AND cs.description='").append(popData.get(1)).append("' "); break;
                    default : strSel.append("AND p.pref").append(key-1).append("='").append(popData.get(key)).append("' ");
                }
            }
        }
        strSel.append((lowPrice != 0 && hiPrice != 0) ? "AND i.hot_price BETWEEN " + lowPrice + " AND " + hiPrice : "");

        Preference mfrName = new Preference();
        Preference conds   = new Preference();
        Preference block1  = new Preference();
        Preference block2  = new Preference();
        Preference block3  = new Preference();
        Preference block4  = new Preference();
        Preference block5  = new Preference();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = null;
            ResultSet rs = null;
//------------------------------
            // make category
            if(categoryID == 0) {
                us = conn.prepareStatement(HOTDEALS_CATEGORY);
                rs = us.executeQuery();
                while(rs.next()) {
                    items.addCategory(new Category(
                        rs.getInt("category_id"),
                        0,
                        rs.getString("name"),
                        rs.getInt("itemsCount") ));
                }
                rs.close();
                us.close();
            }
//------------------------------
            // make subcategory
            else if(categoryID != 0 && subcatID == 0) {
                us = conn.prepareStatement(HOTDEALS_SUBCATEGORY);
                us.setInt(1, categoryID);
                rs = us.executeQuery();
                for(int i = 0; rs.next(); i++) {
                    if(i == 0) {
                        items.setCategoryName(rs.getString("catName"));
                    }
                    items.addSubcategory(new Category(
                        rs.getInt("category_id"),
                        0,
                        rs.getString("scatName"),
                        rs.getInt("itemsCount") ));
                }
                rs.close();
                us.close();
            }
//------------------------------
            // get items and fill preference
            else if(categoryID != 0 && subcatID != 0) {
                us = conn.prepareStatement(HOTDEALS_PREFERENCE + strSel);
                us.setInt(1, subcatID);
                rs = us.executeQuery();
                while(rs.next()) {
                    mfrName.addContent(rs.getString("mfr_name"));
                    conds.addContent(rs.getString("conds"));
                    block1.addContent(rs.getString("pref1"));
                    block2.addContent(rs.getString("pref2"));
                    block3.addContent(rs.getString("pref3"));
                    block4.addContent(rs.getString("pref4"));
                    block5.addContent(rs.getString("pref5"));
                }
                rs.close();
                us.close();
            }
                
//------------------------------
            // get items
            us = conn.prepareStatement(HOTDEALS_GET_ITEMS + strSel + catstr + scatstr + sort + " LIMIT ?,?");
            us.setInt(1, pageNo * Items.LINE_ON_PAGE);
            us.setInt(2, Items.LINE_ON_PAGE);

            rs = us.executeQuery();
            boolean onetime = true;
            while(rs.next()) {
                if(onetime) {
                    onetime = !onetime;

                    mfrName.setTitle("Manufacturer"); items.addPreference(mfrName);
                    conds.setTitle("Condition"); items.addPreference(conds);
                    block1.setTitle(rs.getString("sn1")); items.addPreference(block1);
                    block2.setTitle(rs.getString("sn2")); items.addPreference(block2);
                    block3.setTitle(rs.getString("sn3")); items.addPreference(block3);
                    block4.setTitle(rs.getString("sn4")); items.addPreference(block4);
                    block5.setTitle(rs.getString("sn5")); items.addPreference(block5);

                    items.setCategoryID(rs.getInt("catID"));
                    items.setSubcategoryID(rs.getInt("subcatID"));

                    items.setCategoryName(rs.getString("catName"));
                    items.setSubcategoryName(rs.getString("subcatName"));
                }
                Item item = new Item();

                item.setItemID(rs.getInt("itemID"));
                item.setItemName(rs.getString("itemName"));
                item.setPrice(rs.getDouble("sellPrice"));
                item.setResPrice1(rs.getDouble("resPrice1"));
                item.setResPrice2(rs.getDouble("resPrice2"));
                item.setResQuantity2(rs.getInt("resQuantity2"));
                item.setResPrice3(rs.getDouble("resPrice3"));
                item.setResQuantity3(rs.getInt("resQuantity3"));
                item.setQuantity(rs.getInt("quantity"));
                item.setStock(rs.getBoolean("stock"));
                item.setAccwareCode(rs.getString("accwareCode"));
                item.setMfrName(rs.getString("mfrName"));
                item.setMfrNumber(rs.getString("mfrNumber"));
                item.setBannerMask(rs.getInt("bannerMask"));
                item.setCondition(rs.getString("conditions"));

                items.addItem(item);
            }
            rs.close();
            us.close();

            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() itemsCount");
            rs = us.executeQuery();
            if(rs.next()) {
                items.setItemsCount(rs.getInt("itemsCount"));
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.error("getItemsHotDeals(): " + e);
        }
        items.setEventTime((int)(System.currentTimeMillis() - startEvent));

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsCompuvest()">
// Compuvest Stock aka B2B items
// vendor_id=2 == Compuvest

    public final static String CV_CATEGORY =
        "SELECT count(*) itemsCount,g.category_id category_id,g.name name " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "WHERE i.active='Y' AND im.page_b2b=1 AND g.category_id IS NOT NULL " +
        "GROUP BY g.category_id ORDER BY g.sort,g.name";
    public final static String CV_SUBCATEGORY =
        "SELECT count(*) itemsCount,c.category_id category_id,c.name scatName,g.name catName " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "WHERE i.active='Y' AND im.page_b2b=1 AND c.parent=? " +
        "GROUP BY c.category_id ORDER BY c.sort,c.name";
    public final static String CV_PREFERENCE =
        "SELECT i.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.preference p USING(item_id) " +
        "LEFT JOIN sklad.conditions cs ON " +
        "LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
        "WHERE i.active='Y' AND im.page_b2b=1 AND i.category_id=? ";
    public final static String CV_GET_ITEMS =
        "SELECT SQL_CALC_FOUND_ROWS i.item_id itemID,i.name itemName,i.hot_price sellPrice," +
        "i.res_price1 resPrice1,i.res_price2 resPrice2,i.res_quantity2 resQuantity2,i.res_price3 resPrice3," +
        "i.res_quantity3 resQuantity3," +
        "i.stock_quantity-i.reserv_quantity quantity,IF(i.stock='Y',true,false) stock," +
        "RIGHT(i.accwareCatID,12) accwareCode,i.mfr_name mfrName,i.mfr_number mfrNumber," +
        "g.name catName,c.name subcatName,g.category_id catID,c.category_id subcatID," +
        "c.searchName1 sn1,c.searchName2 sn2,c.searchName3 sn3,c.searchName4 sn4,c.searchName5 sn5," +
        "(im.special | im.closeout<<0x1 | im.commingsoon<<0x2 | im.bestprice<<0x3) bannerMask," +
        "cs.description conditions " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "LEFT JOIN sklad.preference p ON i.item_id=p.item_id " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
        "WHERE i.active='Y' AND im.page_b2b=1 ";

    public Items getItemsCompuvest(
        int categoryID, int subcatID, int sortID, Map<Integer,String> popData, int lowPrice, int hiPrice, int pageNo) {

        long startEvent = System.currentTimeMillis();

        Items items = new Items();

        String catstr  = categoryID == 0 ? "" : " AND c.parent=" + categoryID  + " ";
        String scatstr = subcatID == 0   ? "" : " AND c.category_id=" + subcatID  + " ";

        //sort
        String sort;
        switch(sortID) {
            case  1 : sort = " ORDER BY i.hot_price ASC  "; break;
            case  2 : sort = " ORDER BY i.hot_price DESC "; break;
            default : sort = "";
        }

        // Preference
        StringBuilder strSel = new StringBuilder(200);
        if(popData != null) {
            for(int key : popData.keySet()) {
                switch(key) {
                    case  0 : strSel.append("AND i.mfr_name='").append(popData.get(0)).append("' "); break;
                    case  1 : strSel.append("AND cs.description='").append(popData.get(1)).append("' "); break;
                    default : strSel.append("AND p.pref").append(key-1).append("='").append(popData.get(key)).append("' ");
                }
            }
        }
        strSel.append((lowPrice != 0 && hiPrice != 0) ? "AND i.hot_price BETWEEN " + lowPrice + " AND " + hiPrice : "");

        Preference mfrName = new Preference();
        Preference conds   = new Preference();
        Preference block1  = new Preference();
        Preference block2  = new Preference();
        Preference block3  = new Preference();
        Preference block4  = new Preference();
        Preference block5  = new Preference();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = null;
            ResultSet rs = null;
//------------------------------
            // make category
            if(categoryID == 0) {
                us = conn.prepareStatement(CV_CATEGORY);
                rs = us.executeQuery();
                while(rs.next()) {
                    items.addCategory(new Category(
                        rs.getInt("category_id"),
                        0,
                        rs.getString("name"),
                        rs.getInt("itemsCount") ));
                }
                rs.close();
                us.close();
            }
//------------------------------
            // make subcategory
            else if(categoryID != 0 && subcatID == 0) {
                us = conn.prepareStatement(CV_SUBCATEGORY);
                us.setInt(1, categoryID);
                rs = us.executeQuery();
                for(int i = 0; rs.next(); i++) {
                    if(i == 0) {
                        items.setCategoryName(rs.getString("catName"));
                    }
                    items.addSubcategory(new Category(
                        rs.getInt("category_id"),
                        0,
                        rs.getString("scatName"),
                        rs.getInt("itemsCount") ));
                }
                rs.close();
                us.close();
            }
//------------------------------
            // get items and fill preference
            else if(categoryID != 0 && subcatID != 0) {
                us = conn.prepareStatement(CV_PREFERENCE + strSel);
                us.setInt(1, subcatID);
                rs = us.executeQuery();
                while (rs.next()) {
                    mfrName.addContent(rs.getString("mfr_name"));
                    conds.addContent(rs.getString("conds"));
                    block1.addContent(rs.getString("pref1"));
                    block2.addContent(rs.getString("pref2"));
                    block3.addContent(rs.getString("pref3"));
                    block4.addContent(rs.getString("pref4"));
                    block5.addContent(rs.getString("pref5"));
                }
                rs.close();
                us.close();

                // get items
                us = conn.prepareStatement(CV_GET_ITEMS + strSel + catstr + scatstr + sort + " LIMIT ?,?");
                us.setInt(1, pageNo * Items.LINE_ON_PAGE);
                us.setInt(2, Items.LINE_ON_PAGE);

                rs = us.executeQuery();
                boolean onetime = true;
                while(rs.next()) {
                    if(onetime) {
                        onetime = !onetime;

                        mfrName.setTitle("Manufacturer");
                        items.addPreference(mfrName);
                        conds.setTitle("Condition");
                        items.addPreference(conds);
                        block1.setTitle(rs.getString("sn1"));
                        items.addPreference(block1);
                        block2.setTitle(rs.getString("sn2"));
                        items.addPreference(block2);
                        block3.setTitle(rs.getString("sn3"));
                        items.addPreference(block3);
                        block4.setTitle(rs.getString("sn4"));
                        items.addPreference(block4);
                        block5.setTitle(rs.getString("sn5"));
                        items.addPreference(block5);

                        items.setCategoryID(rs.getInt("catID"));
                        items.setSubcategoryID(rs.getInt("subcatID"));

                        items.setCategoryName(rs.getString("catName"));
                        items.setSubcategoryName(rs.getString("subcatName"));
                    }
                    Item item = new Item();

                    item.setItemID(rs.getInt("itemID"));
                    item.setItemName(rs.getString("itemName"));
                    item.setPrice(rs.getDouble("sellPrice"));
                    item.setResPrice1(rs.getDouble("resPrice1"));
                    item.setResPrice2(rs.getDouble("resPrice2"));
                    item.setResQuantity2(rs.getInt("resQuantity2"));
                    item.setResPrice3(rs.getDouble("resPrice3"));
                    item.setResQuantity3(rs.getInt("resQuantity3"));
                    item.setQuantity(rs.getInt("quantity"));
                    item.setStock(rs.getBoolean("stock"));
                    item.setAccwareCode(rs.getString("accwareCode"));
                    item.setMfrName(rs.getString("mfrName"));
                    item.setMfrNumber(rs.getString("mfrNumber"));
                    item.setBannerMask(rs.getInt("bannerMask"));
                    item.setCondition(rs.getString("conditions"));

                    items.addItem(item);
                }
                rs.close();
                us.close();

                // items count
                us = conn.prepareStatement("SELECT FOUND_ROWS() itemsCount");
                rs = us.executeQuery();
                if(rs.next()) {
                    items.setItemsCount(rs.getInt("itemsCount"));
                }
                rs.close();
                us.close();
            }
        }
        catch(SQLException e) {
            log.error("getItemsCompuvest(): " + e);
        }
        items.setEventTime((int)(System.currentTimeMillis() - startEvent));

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsAll()">

    public final static String ALL_CATEGORY =
        "SELECT count(*) itemsCount,g.category_id category_id,g.name name " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "WHERE i.active='Y' AND g.category_id IS NOT NULL " +
        "GROUP BY g.category_id ORDER BY g.sort,g.name";
    public final static String ALL_SUBCATEGORY =
        "SELECT count(*) itemsCount,c.category_id category_id,c.name scatName,g.name catName " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "WHERE i.active='Y' AND c.parent=? " +
        "GROUP BY c.category_id ORDER BY c.sort,c.name";
    public final static String ALL_PREFERENCE =
        "SELECT i.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.preference p USING(item_id) " +
        "LEFT JOIN sklad.conditions cs ON " +
        "LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
        "WHERE i.active='Y' AND i.category_id=? ";
    public final static String ALL_GET_ITEMS =
        "SELECT SQL_CALC_FOUND_ROWS i.item_id itemID,i.name itemName,i.hot_price sellPrice," +
        "i.res_price1 resPrice1,i.res_price2 resPrice2,i.res_quantity2 resQuantity2,i.res_price3 resPrice3," +
        "i.res_quantity3 resQuantity3," +
        "i.stock_quantity-i.reserv_quantity quantity,IF(i.stock='Y',true,false) stock," +
        "RIGHT(i.accwareCatID,12) accwareCode,i.mfr_name mfrName,i.mfr_number mfrNumber," +
        "g.name catName,c.name subcatName,g.category_id catID,c.category_id subcatID," +
        "c.searchName1 sn1,c.searchName2 sn2,c.searchName3 sn3,c.searchName4 sn4,c.searchName5 sn5," +
        "(im.special | im.closeout<<0x1 | im.commingsoon<<0x2 | im.bestprice<<0x3) bannerMask," +
        "cs.description conditions,im.page_hotdeals hotdeals " +
        "FROM sklad.items i " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "LEFT JOIN sklad.preference p ON i.item_id=p.item_id " +
        "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
        "WHERE i.active='Y' ";

    public Items getItemsAll(
        int categoryID, int subcatID, int sortID, Map<Integer,String> popData, int lowPrice, int hiPrice, int pageNo) {

        long startEvent = System.currentTimeMillis();

        Items items = new Items();

        String catstr  = categoryID == 0 ? "" : " AND c.parent=" + categoryID  + " ";
        String scatstr = subcatID == 0   ? "" : " AND c.category_id=" + subcatID  + " ";

        //sort
        String sort;
        switch(sortID) {
            case  1 : sort = " ORDER BY i.hot_price ASC "; break;
            case  2 : sort = " ORDER BY i.hot_price DESC"; break;
            case  3 : sort = " AND im.page_hotdeals=1 ORDER BY i.hot_price ASC "; break;
            case  4 : sort = " AND im.page_hotdeals=1 ORDER BY i.hot_price DESC"; break;
            default : sort = "";
        }

        // Preference
        StringBuilder strSel = new StringBuilder(200);
        if(popData != null) {
            for(int key : popData.keySet()) {
                switch(key) {
                    case  0 : strSel.append("AND i.mfr_name='").append(popData.get(0)).append("' "); break;
                    case  1 : strSel.append("AND cs.description='").append(popData.get(1)).append("' "); break;
                    default : strSel.append("AND p.pref").append(key-1).append("='").append(popData.get(key)).append("' ");
                }
            }
        }
        strSel.append((lowPrice != 0 && hiPrice != 0) ? "AND i.hot_price BETWEEN " + lowPrice + " AND " + hiPrice : "");

        Preference mfrName = new Preference();
        Preference conds   = new Preference();
        Preference block1  = new Preference();
        Preference block2  = new Preference();
        Preference block3  = new Preference();
        Preference block4  = new Preference();
        Preference block5  = new Preference();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = null;
            ResultSet rs = null;
//------------------------------
            // make category
            if(categoryID == 0) {
                us = conn.prepareStatement(ALL_CATEGORY);
                rs = us.executeQuery();
                while(rs.next()) {
                    items.addCategory(new Category(
                        rs.getInt("category_id"),
                        0,
                        rs.getString("name"),
                        rs.getInt("itemsCount") ));
                }
                rs.close();
                us.close();
            }
//------------------------------
            // make subcategory
            else if(categoryID != 0 && subcatID == 0) {
                us = conn.prepareStatement(ALL_SUBCATEGORY);
                us.setInt(1, categoryID);
                rs = us.executeQuery();
                for(int i = 0; rs.next(); i++) {
                    if(i == 0) {
                        items.setCategoryName(rs.getString("catName"));
                    }
                    items.addSubcategory(new Category(
                        rs.getInt("category_id"),
                        0,
                        rs.getString("scatName"),
                        rs.getInt("itemsCount") ));
                }
                rs.close();
                us.close();
            }
//------------------------------
            // get items and fill preference
            else if(categoryID != 0 && subcatID != 0) {
                us = conn.prepareStatement(ALL_PREFERENCE + strSel);
                us.setInt(1, subcatID);
                rs = us.executeQuery();
                while(rs.next()) {
                    mfrName.addContent(rs.getString("mfr_name"));
                    conds.addContent(rs.getString("conds"));
                    block1.addContent(rs.getString("pref1"));
                    block2.addContent(rs.getString("pref2"));
                    block3.addContent(rs.getString("pref3"));
                    block4.addContent(rs.getString("pref4"));
                    block5.addContent(rs.getString("pref5"));
                }
                rs.close();
                us.close();

                // get items
                us = conn.prepareStatement(ALL_GET_ITEMS + strSel + catstr + scatstr + sort + " LIMIT ?,?");
                us.setInt(1, pageNo * Items.LINE_ON_PAGE);
                us.setInt(2, Items.LINE_ON_PAGE);

                rs = us.executeQuery();
                boolean oneloop = true;
                while(rs.next()) {
                    if(oneloop) {
                        oneloop = false; // first loop only

                        mfrName.setTitle("Manufacturer"); items.addPreference(mfrName);
                        conds.setTitle("Condition"); items.addPreference(conds);
                        block1.setTitle(rs.getString("sn1")); items.addPreference(block1);
                        block2.setTitle(rs.getString("sn2")); items.addPreference(block2);
                        block3.setTitle(rs.getString("sn3")); items.addPreference(block3);
                        block4.setTitle(rs.getString("sn4")); items.addPreference(block4);
                        block5.setTitle(rs.getString("sn5")); items.addPreference(block5);

                        items.setCategoryID(rs.getInt("catID"));
                        items.setSubcategoryID(rs.getInt("subcatID"));

                        items.setCategoryName(rs.getString("catName"));
                        items.setSubcategoryName(rs.getString("subcatName"));
                    }
                    Item item = new Item();
                    if(rs.getBoolean("hotdeals")) {
                        items.setHotDeals(); // for items page sort
                    }

                    item.setItemID(rs.getInt("itemID"));
                    item.setItemName(rs.getString("itemName"));
                    item.setPrice(rs.getDouble("sellPrice"));
                    item.setResPrice1(rs.getDouble("resPrice1"));
                    item.setResPrice2(rs.getDouble("resPrice2"));
                    item.setResQuantity2(rs.getInt("resQuantity2"));
                    item.setResPrice3(rs.getDouble("resPrice3"));
                    item.setResQuantity3(rs.getInt("resQuantity3"));
                    item.setQuantity(rs.getInt("quantity"));
                    item.setStock(rs.getBoolean("stock"));
                    item.setAccwareCode(rs.getString("accwareCode"));
                    item.setMfrName(rs.getString("mfrName"));
                    item.setMfrNumber(rs.getString("mfrNumber"));
                    item.setBannerMask(rs.getInt("bannerMask"));
                    item.setCondition(rs.getString("conditions"));

                    items.addItem(item);
                }
                rs.close();
                us.close();

                // items count
                us = conn.prepareStatement("SELECT FOUND_ROWS() itemsCount");
                rs = us.executeQuery();
                if(rs.next()) {
                    items.setItemsCount(rs.getInt("itemsCount"));
                }
                rs.close();
                us.close();
            }
        }
        catch(SQLException e) {
            log.error("getItemsAll(): " + e);
        }
        items.setEventTime((int)(System.currentTimeMillis() - startEvent));

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsSearch()">

    public Items getItemsSearch(
        int categoryID, int subcatID, int sortID, Map<Integer,String> popData, int lowPrice, int hiPrice, int pageNo, int searchID, String search) {

        long startEvent = System.currentTimeMillis();

        Items items = new Items();

        if(search.length() < 3 && !search.contains("hp")) { return items; }

        search = search.toLowerCase().trim().replaceAll("[^a-z0-9\\-\\+\\=\\#\\.]+", " ");
        search = search.replaceAll("([\\-]+)|([\\#]+)|([\\.]+)", "").trim(); // except

        if(search.length() == 0) { return items; }

        StringBuilder query = new StringBuilder(400);
        String[] split = search.split("\\p{Space}"); // parse search string
        if(split.length == 1) {
            query.append(search).append('*');
        }
        else {
            for(String word : split) {
                if("hp".equals(word)) { word = "hpp"; }
                else if(word.length() < 3 || word.length() > 25) continue;

                query.append('+').append(word).append(' ');
            }

            if(query.length() == 0) { return items; }
        }

        String catstr  = categoryID == 0 ? "" : " AND c.parent=" + categoryID  + " ";
        String scatstr = subcatID == 0   ? "" : " AND c.category_id=" + subcatID  + " ";

        // sort
        String sort;
        switch(sortID) {
            case  1 : sort = " ORDER BY i.hot_price ASC  "; break;
            case  2 : sort = " ORDER BY i.hot_price DESC "; break;
            default : sort = "";
        }

        // Preference
        StringBuilder strSel = new StringBuilder(200);
        if(popData != null) {
            for(int key : popData.keySet()) {
                switch(key) {
                    case  0 : strSel.append("AND i.mfr_name='").append(popData.get(0)).append("' "); break;
                    case  1 : strSel.append("AND cs.description='").append(popData.get(1)).append("' "); break;
                    default : strSel.append("AND p.pref").append(key-1).append("='").append(popData.get(key)).append("' ");
                }
            }
        }
        strSel.append((lowPrice != 0 && hiPrice != 0) ? "AND i.hot_price BETWEEN " + lowPrice + " AND " + hiPrice : "");

        Preference mfrName = new Preference();
        Preference conds   = new Preference();
        Preference block1  = new Preference();
        Preference block2  = new Preference();
        Preference block3  = new Preference();
        Preference block4  = new Preference();
        Preference block5  = new Preference();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = null;
            ResultSet rs = null;
//------------------------------
            // make category
            if(categoryID == 0) {
                us = conn.prepareStatement(
                    "SELECT count(*) itemsCount,g.category_id category_id,g.name name " +
                    "FROM sklad.items i " +
                    "LEFT JOIN sklad.category c USING(category_id) " +
                    "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
                    "WHERE i.active='Y' AND g.category_id IS NOT NULL " +
                    "AND MATCH(i.keywords) AGAINST('" + query + "' IN BOOLEAN MODE) " +
                    "GROUP BY g.category_id ORDER BY g.sort,g.name"
                );
                rs = us.executeQuery();
                while(rs.next()) {
                    items.addCategory(new Category(
                        rs.getInt("category_id"),
                        0,
                        rs.getString("name"),
                        rs.getInt("itemsCount") ));
                }
                rs.close();
                us.close();
            }

//------------------------------
            // make subcategory
            else if(categoryID != 0 && subcatID == 0) {
                us = conn.prepareStatement(
                    "SELECT count(*) itemsCount,c.category_id category_id,c.name scatName,g.name catName " +
                    "FROM sklad.items i " +
                    "LEFT JOIN sklad.category c USING(category_id) " +
                    "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
                    "WHERE i.active='Y' AND c.parent=? " +
                    "AND MATCH(i.keywords) AGAINST('" + query + "' IN BOOLEAN MODE) " +
                    "GROUP BY c.category_id ORDER BY c.sort,c.name"
                );
                us.setInt(1, categoryID);
                rs = us.executeQuery();
                for(int i = 0; rs.next(); i++) {
                    if(i == 0) {
                        items.setCategoryName(rs.getString("catName"));
                    }
                    items.addSubcategory(new Category(
                        rs.getInt("category_id"),
                        0,
                        rs.getString("scatName"),
                        rs.getInt("itemsCount") ));
                }
                rs.close();
                us.close();
            }
//------------------------------
            // fill preference
            else if(categoryID != 0 && subcatID != 0) {
                us = conn.prepareStatement(
                    "SELECT i.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 " +
                    "FROM sklad.items i " +
                    "LEFT JOIN sklad.preference p USING(item_id) " +
                    "LEFT JOIN sklad.conditions cs ON " +
                    "LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
                    "WHERE i.active='Y' AND i.category_id=? " +
                    "AND MATCH(i.keywords) AGAINST('" + query + "' IN BOOLEAN MODE) " +
                    strSel
                );
                us.setInt(1, subcatID);
                rs = us.executeQuery();
                while(rs.next()) {
                    mfrName.addContent(rs.getString("mfr_name"));
                    conds.addContent(rs.getString("conds"));
                    block1.addContent(rs.getString("pref1"));
                    block2.addContent(rs.getString("pref2"));
                    block3.addContent(rs.getString("pref3"));
                    block4.addContent(rs.getString("pref4"));
                    block5.addContent(rs.getString("pref5"));
                }
                rs.close();
                us.close();
            }


            // get items
            us = conn.prepareStatement(
                "SELECT SQL_CALC_FOUND_ROWS i.item_id itemID,i.name itemName,i.hot_price sellPrice," +
                "i.res_price1 resPrice1,i.res_price2 resPrice2,i.res_quantity2 resQuantity2,i.res_price3 resPrice3," +
                "i.res_quantity3 resQuantity3," +
                "i.stock_quantity-i.reserv_quantity quantity,IF(i.stock='Y',true,false) stock," +
                "RIGHT(i.accwareCatID,12) accwareCode,i.mfr_name mfrName,i.mfr_number mfrNumber," +
                "c.name subcatName,g.name catName," +
                "c.searchName1 sn1,c.searchName2 sn2,c.searchName3 sn3,c.searchName4 sn4,c.searchName5 sn5," +
                "(im.special | im.closeout<<0x1 | im.commingsoon<<0x2 | im.bestprice<<0x3) bannerMask," +
                "cs.description conditions " +
                "FROM sklad.items i " +
                "LEFT JOIN sklad.itemsMask im USING(item_id) " +
                "LEFT JOIN sklad.category c USING(category_id) " +
                "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
                "LEFT JOIN sklad.preference p ON i.item_id=p.item_id " +
                "LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id " +
                "WHERE i.active='Y' " +
                "AND MATCH(i.keywords) AGAINST('" + query + "' IN BOOLEAN MODE) " +
                strSel + catstr + scatstr + sort + " LIMIT ?,?");
            us.setInt(1, pageNo * Items.LINE_ON_PAGE);
            us.setInt(2, Items.LINE_ON_PAGE);

            rs = us.executeQuery();
            boolean onetime = true;
            while(rs.next()) {
                if(onetime) {
                    onetime = !onetime;
                    if(subcatID != 0) {
                        mfrName.setTitle("Manufacturer"); items.addPreference(mfrName);
                        conds.setTitle("Condition"); items.addPreference(conds);
                        block1.setTitle(rs.getString("sn1")); items.addPreference(block1);
                        block2.setTitle(rs.getString("sn2")); items.addPreference(block2);
                        block3.setTitle(rs.getString("sn3")); items.addPreference(block3);
                        block4.setTitle(rs.getString("sn4")); items.addPreference(block4);
                        block5.setTitle(rs.getString("sn5")); items.addPreference(block5);
                    }
                    items.setCategoryName(rs.getString("catName"));
                    items.setSubcategoryName(rs.getString("subcatName"));
                }
                Item item = new Item();

                item.setItemID(rs.getInt("itemID"));
                item.setItemName(rs.getString("itemName"));
                item.setPrice(rs.getDouble("sellPrice"));
                item.setResPrice1(rs.getDouble("resPrice1"));
                item.setResPrice2(rs.getDouble("resPrice2"));
                item.setResQuantity2(rs.getInt("resQuantity2"));
                item.setResPrice3(rs.getDouble("resPrice3"));
                item.setResQuantity3(rs.getInt("resQuantity3"));
                item.setQuantity(rs.getInt("quantity"));
                item.setStock(rs.getBoolean("stock"));
                item.setAccwareCode(rs.getString("accwareCode"));
                item.setMfrName(rs.getString("mfrName"));
                item.setMfrNumber(rs.getString("mfrNumber"));
                item.setBannerMask(rs.getInt("bannerMask"));
                item.setCondition(rs.getString("conditions"));

                items.addItem(item);
            }
            rs.close();
            us.close();

            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() itemsCount");
            rs = us.executeQuery();
            if(rs.next()) {
                items.setItemsCount(rs.getInt("itemsCount"));
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.error("getItemsSearch(): " + e);
        }
        items.setEventTime((int)(System.currentTimeMillis() - startEvent));

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItemsReseller()">

    public Items getItemsReseller(int categoryID, int subcatID, String sort, String direct, Map<Integer,String> popData, int pageNo) {

        long startEvent = System.currentTimeMillis();

        Items items = new Items();

        String catstr = categoryID == 0 ? "" : " AND c.parent=" + categoryID  + " ";
        String scatstr = subcatID == 0 ? "" : " AND c.category_id=" + subcatID  + " ";
        String sortstr = sort.length() == 0 ? "vendor_id=" + VENDOR_CVS + " DESC,i.hot_price ASC" : sort + " " + direct;

        // preference
        StringBuilder strSel = new StringBuilder(200);
        if(popData != null) {
            for(int key : popData.keySet()) {
                switch(key) {
                    case  0 : strSel.append("AND i.mfr_name='").append(popData.get(0)).append("' "); break;
                    case  1 : strSel.append("AND cs.description='").append(popData.get(1)).append("' "); break;
                    default : strSel.append("AND p.pref").append(key-1).append("='").append(popData.get(key)).append("' "); break;
                }
            }
        }

        Preference mfrName = new Preference();
        Preference conds = new Preference();
        Preference sr1 = new Preference();
        Preference sr2 = new Preference();
        Preference sr3 = new Preference();
        Preference sr4 = new Preference();
        Preference sr5 = new Preference();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = null;
            ResultSet rs = null;

//------------------------------------
            if(subcatID != 0) {
                // items count and fill preference
                us = conn.prepareStatement(new StringBuilder(400).
                    append("SELECT i.mfr_name mfr_name,cs.description conds,p.pref1 pref1,p.pref2 pref2,p.pref3 pref3,p.pref4 pref4,p.pref5 pref5 ").
                    append("FROM sklad.items i ").
                    append("LEFT JOIN sklad.preference p USING(item_id) ").
                    append("LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id ").
                    append("WHERE i.active='Y' AND i.category_id=? ").append(strSel).
                    append(" AND i.res_price1<>0").toString());
                us.setInt(1, subcatID);

                rs = us.executeQuery();
                while(rs.next()) {
                    mfrName.addContent(rs.getString("mfr_name"));
                    conds.addContent(rs.getString("conds"));
                    sr1.addContent(rs.getString("pref1"));
                    sr2.addContent(rs.getString("pref2"));
                    sr3.addContent(rs.getString("pref3"));
                    sr4.addContent(rs.getString("pref4"));
                    sr5.addContent(rs.getString("pref5"));
                }
                rs.close();
                us.close();
            }

            // make category
            us = conn.prepareStatement(new StringBuilder(500).
                append("SELECT count(*) itemsCount,g.category_id category_id,g.name name ").
                append("FROM sklad.items i ").
                append("LEFT JOIN sklad.category c USING(category_id) ").
                append("LEFT JOIN sklad.category g ON c.parent=g.category_id ").
                append("WHERE i.active='Y' AND i.res_price1<>0 AND g.category_id IS NOT NULL ").
                append("GROUP BY g.category_id ORDER BY g.sort,g.name").toString());

            rs = us.executeQuery();
            while(rs.next()) {
                items.addCategory(new Category(rs.getInt("category_id"), 0, rs.getString("name"), rs.getInt("itemsCount")));
            }
            rs.close();
            us.close();

            // make subcategory
            if(categoryID != 0) {
                us = conn.prepareStatement(new StringBuilder(500).
                    append("SELECT count(*) itemsCount,c.category_id category_id,c.name name ").
                    append("FROM sklad.items i ").
                    append("LEFT JOIN sklad.category c USING(category_id) ").
                    append("WHERE i.active='Y' AND i.res_price1<>0 AND c.parent=? ").
                    append("GROUP BY c.category_id ORDER BY c.sort,c.name").toString());
                us.setInt(1, categoryID);

                rs = us.executeQuery();
                while(rs.next()) {
                    items.addSubcategory(new Category(rs.getInt("category_id"), 0, rs.getString("name"), rs.getInt("itemsCount")));
                }
                rs.close();
                us.close();
            }

            // items
            us = conn.prepareStatement(new StringBuilder(1800).
                append("SELECT SQL_CALC_FOUND_ROWS i.item_id itemID,i.name itemName,i.hot_price sellPrice,i.res_price1 resPrice1,").
                append("i.res_quantity2 resQuantity2,i.res_price2 resPrice2,i.res_quantity3 resQuantity3,i.res_price3 resPrice3,").
                append("i.stock_quantity-i.reserv_quantity quantity,IF(i.stock='Y',true,false) stock,").
                append("RIGHT(i.accwareCatID,12) accwareCode,i.mfr_name mfrName,i.mfr_number mfrNumber,").

                append("c.searchName1 sn1,c.searchName2 sn2,c.searchName3 sn3,c.searchName4 sn4,c.searchName5 sn5,").

                append("(im.special | im.closeout | im.commingsoon | im.bestprice) bannerMask,cs.description conditions,").
                append("c.name subcatName,g.name catName ").

                append("FROM sklad.items i ").
                append("LEFT JOIN sklad.itemsMask im USING(item_id) ").
                append("LEFT JOIN sklad.category c USING(category_id) ").
                append("LEFT JOIN sklad.category g ON c.parent=g.category_id ").
                append("LEFT JOIN sklad.preference p ON i.item_id=p.item_id ").
                append("LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(i.accwareCatID,2),1)=cs.id ").
                append("WHERE i.active='Y' AND i.res_price1<>0 ").
                append(strSel).append(catstr).append(scatstr).
                append(" ORDER BY i.").	append(sortstr).append(" LIMIT ?,?").toString());
            us.setInt(1, pageNo * Items.LINE_ON_PAGE);
            us.setInt(2, Items.LINE_ON_PAGE);

            rs = us.executeQuery();
            for(int i = 0; rs.next(); i++) {
                if(i == 0) {
                    if(subcatID != 0) {
                        mfrName.setTitle("Manufacturer"); items.addPreference(mfrName);
                        conds.setTitle("Condition"); items.addPreference(conds);
                        sr1.setTitle(rs.getString("sn1")); items.addPreference(sr1);
                        sr2.setTitle(rs.getString("sn2")); items.addPreference(sr2);
                        sr3.setTitle(rs.getString("sn3")); items.addPreference(sr3);
                        sr4.setTitle(rs.getString("sn4")); items.addPreference(sr4);
                        sr5.setTitle(rs.getString("sn5")); items.addPreference(sr5);
                    }
                    items.setCategoryName(rs.getString("catName"));
                    items.setSubcategoryName(rs.getString("subcatName"));
                }
                Item item = new Item();

                item.setItemID(rs.getInt("itemID"));
                item.setItemName(rs.getString("itemName"));
                item.setPrice(rs.getDouble("sellPrice"));
                item.setResPrice1(rs.getDouble("resPrice1"));
                item.setResPrice2(rs.getDouble("resPrice2"));
                item.setResQuantity2(rs.getInt("resQuantity2"));
                item.setResPrice3(rs.getDouble("resPrice3"));
                item.setResQuantity3(rs.getInt("resQuantity3"));
                item.setQuantity(rs.getInt("quantity"));
                item.setStock(rs.getBoolean("stock"));
                item.setAccwareCode(rs.getString("accwareCode"));
                item.setMfrName(rs.getString("mfrName"));
                item.setMfrNumber(rs.getString("mfrNumber"));
                item.setBannerMask(rs.getInt("bannerMask"));
                item.setCondition(rs.getString("conditions"));

                items.addItem(item);
            }
            rs.close();
            us.close();

            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() itemsCount");
            rs = us.executeQuery();
            if(rs.next()) {
                items.setItemsCount(rs.getInt("itemsCount"));
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.error("getItemsReseler(): " + e);
        }
        items.setEventTime((int)(System.currentTimeMillis() - startEvent));

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="checkCustomer()">
// return -1: (customer present)   0: new customer

    public int checkCustomer(String email) {

        int result = 0;

        try(Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement("SELECT customer_id FROM sklad.customer WHERE email=LOWER(?)"))
        {
            us.setString(1, email);
            try (ResultSet rs = us.executeQuery())
            {
                if(rs.next()) {
                    result = -1;
                }
            }
        }
        catch(SQLException e) {
            log.error("checkCustomer(): " + e);
        }

        return result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getCustomer()">

    final static String SELECT_CUSTOMER = "SELECT customer_id,email FROM sklad.customer WHERE email=LOWER(?)";
    final static String SELECT_CUSTOMER_OK =
	"SELECT customer_id,type,reseller_code,accwareAccount,email,password," +
	"shippingCompany,shippingFirstName,shippingLastName,shipping_address1,shipping_address2,shipping_city," +
        "shipping_state,shipping_country,shipping_zip,shipping_zip4,shippingPhone,shippingCell,shippingFax," +
        "billingCompany,billingFirstName,billingLastName,billing_address1,billing_address2,billing_city," +
        "billing_state,billing_country,billing_zip,billing_zip4,billingPhone,billingCell,billingFax," +
        "cc_name,ENCODE(cardNumber,'crypt') cardNumber,cc_number_add," +
        "left(cc_exp_date,2) month,right(cc_exp_date,2) year,bankPhone," +
        "notice,joined " +
        "FROM sklad.customer WHERE email=LOWER(?) AND password=?";

	public Customer getCustomer(String email, String password) {

        Customer cust = new Customer();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(SELECT_CUSTOMER);
            us.setString(1, email);

            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                cust.setCustomerID(rs.getInt("customer_id"));
            }
            else { // no users
                cust.setCustomerID(-1);
            }
            rs.close();
            us.close();

            if(cust.getCustomerID() > 0) {

                us = conn.prepareStatement(SELECT_CUSTOMER_OK);
                us.setString(1, email);
                us.setString(2, password);

                rs = us.executeQuery();
                if(rs.next()) { //email and password -> OK

                    cust.setType(rs.getString("type"));
                    cust.setResellerCode(rs.getString("reseller_code"));
                    cust.setAccwareAccount(rs.getString("accwareAccount"));
                    cust.setEmail(rs.getString("email"));
                    cust.setPassword(rs.getString("password"));

                    cust.setShippingAddress(new Address(
                            rs.getString("shippingCompany"),
                            rs.getString("shippingFirstName"),
                            rs.getString("shippingLastName"),
                            rs.getString("shipping_address1"),
                            rs.getString("shipping_address2"),
                            rs.getString("shipping_city"),
                            rs.getString("shipping_country"),
                            rs.getString("shipping_state"),
                            rs.getString("shipping_zip"),
                            rs.getString("shipping_zip4"),
                            rs.getString("shippingPhone"),
                            rs.getString("shippingCell"),
                            rs.getString("shippingFax")));

                    cust.setBillingAddress(new Address(
                            rs.getString("billingCompany"),
                            rs.getString("billingFirstName"),
                            rs.getString("billingLastName"),
                            rs.getString("billing_address1"),
                            rs.getString("billing_address2"),
                            rs.getString("billing_city"),
                            rs.getString("billing_country"),
                            rs.getString("billing_state"),
                            rs.getString("billing_zip"),
                            rs.getString("billing_zip4"),
                            rs.getString("billingPhone"),
                            rs.getString("billingCell"),
                            rs.getString("billingFax")));

                    cust.setCName(rs.getString("cc_name"));
                    cust.setCardNumber(rs.getString("cardNumber"));
                    cust.setCNumberAdd(rs.getInt("cc_number_add"));
                    cust.setCExpMonth(rs.getString("month"));
                    cust.setCExpYears(rs.getString("year"));
                    cust.setBankPhone(rs.getString("bankPhone"));

                    cust.setNotice(rs.getString("notice"));
                    cust.setJoined(rs.getString("joined"));

                    rs.close();
                    us.close();
		}
                else { // email = OK passwd = NOT
                    cust.setCustomerID(0);
                }
            }

	}
        catch(SQLException e) {
            log.error("getCustomer(): " + e);
        }

        return cust;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="newCustomer()">

    public void newCustomer(Customer cust, String ipAddress) {

        try(Connection conn = ds.getConnection()) {

            int pos = 1;
            CallableStatement cs =
                conn.prepareCall("{ call sklad.updateCustomerB2B(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }");
            cs.setString(pos++, cust.getEmail());
            cs.setString(pos++, cust.getPassword());
            cs.setString(pos++, cust.getType());
            cs.setString(pos++, ""); // resellerCode
       	    cs.setString(pos++, cust.getShippingAccount());

            cs.setString(pos++, cust.shipping.getCompany());
            cs.setString(pos++, cust.shipping.getFirstName());
            cs.setString(pos++, cust.shipping.getLastName());
            cs.setString(pos++, cust.shipping.getAddress1());
            cs.setString(pos++, cust.shipping.getAddress2());
            cs.setString(pos++, cust.shipping.getCity());
            cs.setString(pos++, cust.shipping.getState());
            cs.setString(pos++, cust.shipping.getCountry());
            cs.setString(pos++, cust.shipping.getZip());
            cs.setString(pos++, cust.shipping.getZip4());
            cs.setString(pos++, cust.shipping.getPhone());
            cs.setString(pos++, cust.shipping.getCell());
            cs.setString(pos++, cust.shipping.getFax());

            cs.setString(pos++, cust.billing.getCompany());
            cs.setString(pos++, cust.billing.getFirstName());
            cs.setString(pos++, cust.billing.getLastName());
            cs.setString(pos++, cust.billing.getAddress1());
            cs.setString(pos++, cust.billing.getAddress2());
            cs.setString(pos++, cust.billing.getCity());
            cs.setString(pos++, cust.billing.getState());
            cs.setString(pos++, cust.billing.getCountry());
            cs.setString(pos++, cust.billing.getZip());
            cs.setString(pos++, cust.billing.getZip4());
            cs.setString(pos++, cust.billing.getPhone());
            cs.setString(pos++, cust.billing.getCell());
            cs.setString(pos++, cust.billing.getFax());

            cs.setString(pos++, cust.getCName());
            cs.setString(pos++, cust.getCType());
            cs.setString(pos++, cust.getCardNumber());
            cs.setInt(pos++, cust.getCNumberAdd());
            cs.setString(pos++, cust.getCExpDate());
            cs.setString(pos++, cust.getBankPhone());

            cs.setString(pos++, cust.getNotice());
            cs.setString(pos++, ""); // orderNumber
            cs.setInt(pos++, 0); // count number
            cs.setDouble(pos++, 0d); // order total
            cs.setString(pos++, ipAddress);

            cs.registerOutParameter(pos, Types.INTEGER);

            cs.executeUpdate();

            cs.close();
        }
        catch(SQLException e) {
            log.error("newCustomer(): " + e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="newReseller()">

    public void newReseller(Customer cust, String ipAddress) {

        try(Connection conn = ds.getConnection()) {

            int pos = 1;
            CallableStatement cs = conn.prepareCall(
                "{ call sklad.updateCustomerB2B(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }");
            cs.setString(pos++, cust.getEmail());
            cs.setString(pos++, cust.getPassword());
            cs.setString(pos++, cust.getType());
            cs.setString(pos++, cust.getResellerCode());
            cs.setString(pos++, ""); // cust.getShippingAccount()

            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");
            cs.setString(pos++, "");

            cs.setString(pos++, cust.billing.getCompany());
            cs.setString(pos++, cust.billing.getFirstName());
            cs.setString(pos++, cust.billing.getLastName());
            cs.setString(pos++, cust.billing.getAddress1());
            cs.setString(pos++, cust.billing.getAddress2());
            cs.setString(pos++, cust.billing.getCity());
            cs.setString(pos++, cust.billing.getState());
            cs.setString(pos++, cust.billing.getCountry());
            cs.setString(pos++, cust.billing.getZip());
            cs.setString(pos++, cust.billing.getZip4());
            cs.setString(pos++, cust.billing.getPhone());
            cs.setString(pos++, cust.billing.getCell());
            cs.setString(pos++, cust.billing.getFax());

            cs.setString(pos++, "");
            cs.setString(pos++, "VISA");
            cs.setString(pos++, "");
            cs.setInt(pos++, 0);
            cs.setString(pos++, "");
            cs.setString(pos++, "");

            cs.setString(pos++, cust.getNotice());
            cs.setString(pos++, ""); // orderNumber
            cs.setInt(pos++, 0); // count number
            cs.setDouble(pos++, 0d); // order total
            cs.setString(pos++, ipAddress);

            cs.registerOutParameter(pos, Types.INTEGER);

            cs.executeUpdate();

            cs.close();

            //int custID = cs.getInt(pos);
            //System.out.println("newReseller:custID=" + cs.getInt(pos));
        }
        catch(SQLException e) {
            log.error("newReseller(): " + e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getPassword()">
//
// return: true  - send password by email
//         false - customer not present

    public Customer getPassword(String email) {

        Customer cust = new Customer();

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(
                "SELECT customer_id,email,password,billingFirstName,billingLastName FROM sklad.customer WHERE email=LOWER(?)");
            us.setString(1, email);

            ResultSet rs = us.executeQuery();
            if(rs.next()) {

                cust.setCustomerID(rs.getInt("customer_id"));
                cust.setEmail(rs.getString("email"));
                cust.setPassword(rs.getString("password"));

                Address billing = new Address();
                billing.setFirstName(rs.getString("billingFirstName"));
                billing.setLastName(rs.getString("billingLastName"));

                cust.setBillingAddress(billing);
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.error("getPassword(): " + e);
        }

        return cust;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="copyToHistory()">

    public void copyToHistory(int orderID, String orderCopy) {

        if(orderCopy.length() == 0 || orderID == 0) { return; }

        try(Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement("INSERT LOW_PRIORITY sklad.orderHistory SET order_id:=?,order_cp:=?"))
        {
            us.setInt(1, orderID);
            us.setString(2, orderCopy);

            us.executeUpdate();
        }
        catch(SQLException e) {
            log.error("copyToHistory(): " + e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeOrder()">

    public String makeOrder(Basket basket) {

        if(basket.size() == 0) { return ""; }

        Customer cust = basket.getCustomer();

        try(Connection conn = ds.getConnection()) {

            CallableStatement cs = conn.prepareCall("{ call sklad.getOrderNumber() }");
            ResultSet rs = cs.executeQuery();
            if(rs.next()) {
                basket.setOrderNumber("BB" + rs.getString(1));
            }
            rs.close();
            cs.close();

            int pos = 1;
            cs = conn.prepareCall(
                "{ call sklad.updateCustomerB2B(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }");
            cs.setString(pos++, cust.getEmail());
            cs.setString(pos++, cust.getPassword());
            cs.setString(pos++, cust.getType());
            cs.setString(pos++, cust.getResellerCode());
            cs.setString(pos++, cust.getShippingAccount());

            cs.setString(pos++, cust.shipping.getCompany());
            cs.setString(pos++, cust.shipping.getFirstName());
            cs.setString(pos++, cust.shipping.getLastName());
            cs.setString(pos++, cust.shipping.getAddress1());
            cs.setString(pos++, cust.shipping.getAddress2());
            cs.setString(pos++, cust.shipping.getCity());
            cs.setString(pos++, cust.shipping.getState());
            cs.setString(pos++, cust.shipping.getCountry());
            cs.setString(pos++, cust.shipping.getZip());
            cs.setString(pos++, cust.shipping.getZip4());
            cs.setString(pos++, cust.shipping.getPhone());
            cs.setString(pos++, cust.shipping.getCell());
            cs.setString(pos++, cust.shipping.getFax());

            cs.setString(pos++, cust.billing.getCompany());
            cs.setString(pos++, cust.billing.getFirstName());
            cs.setString(pos++, cust.billing.getLastName());
            cs.setString(pos++, cust.billing.getAddress1());
            cs.setString(pos++, cust.billing.getAddress2());
            cs.setString(pos++, cust.billing.getCity());
            cs.setString(pos++, cust.billing.getState());
            cs.setString(pos++, cust.billing.getCountry());
            cs.setString(pos++, cust.billing.getZip());
            cs.setString(pos++, cust.billing.getZip4());
            cs.setString(pos++, cust.billing.getPhone());
            cs.setString(pos++, cust.billing.getCell());
            cs.setString(pos++, cust.billing.getFax());

            cs.setString(pos++, cust.getCName());
            cs.setString(pos++, cust.getCType());
            cs.setString(pos++, cust.getCardNumber());
            cs.setInt(pos++, cust.getCNumberAdd());
            cs.setString(pos++, cust.getCExpDate());
            cs.setString(pos++, cust.getBankPhone());

            cs.setString(pos++, cust.getNotice());
            cs.setString(pos++, basket.getOrderNumber());
            cs.setInt(pos++, 1); // count number
            cs.setDouble(pos++, basket.getGrandTotal());
            cs.setString(pos++, basket.getClickstream().getIPAddress());

            cs.registerOutParameter(pos, Types.INTEGER);

            cs.executeUpdate();
            cust.setCustomerID(cs.getInt(pos));
            cs.close();

            pos = 1;
            PreparedStatement us = conn.prepareStatement(new StringBuilder(500).
            append("INSERT sklad.orders SET order_date=CURRENT_TIMESTAMP(),orderNo=?,customer_id=?,").
            append("blindShipping=?,packingSlip=?,customerPO=?,shipping_price=?,shipping_code=?,cod=?,paymentMethod=?,").
            append("tax_price=?,amount=?,advert_id=1,notes=?,trans_fee=?,cjpid=?,cjaid=?,cjsid=?").toString());

            us.setString(pos++, basket.getOrderNumber());
            us.setInt(pos++, cust.getCustomerID());
            us.setBoolean(pos++, basket.getBlindShipping());
            us.setBoolean(pos++, basket.getPackingSlip());
            us.setString(pos++, basket.getPurchaseOrder());
            us.setDouble(pos++, basket.getShippingCost());
            us.setString(pos++, basket.getShippingCode());
            us.setString(pos++, basket.getPaymentMethod().equals("CD") ? "Y" : "N");
            us.setString(pos++, basket.getPaymentMethod());
            us.setDouble(pos++, 0d);//us.setDouble(pos++, basket.getTaxCost());
            us.setDouble(pos++, basket.getGrandTotal());
            us.setString(pos++, basket.getNotes());
            us.setDouble(pos++, basket.getTransFee());
            us.setString(pos++, basket.getCookieBean().getCJPid());
            us.setString(pos++, basket.getCookieBean().getCJAid());
            us.setString(pos, basket.getCookieBean().getCJSid());

            us.executeUpdate();
            us.close();

            us = conn.prepareStatement("SELECT LAST_INSERT_ID()");
            rs = us.executeQuery();
            if(rs.next()) {
                basket.setOrderID(rs.getInt(1));
            }
            rs.close();
            us.close();

            us = conn.prepareStatement(
                "INSERT sklad.order_line SET order_id=?,item_id=?,quantity=?,sell_price=?,warrantyDesc=?,warrantyPrice=?");
            PreparedStatement us2 = conn.prepareStatement(
                "UPDATE sklad.items SET sell_quantity=sell_quantity+?,reserv_quantity=reserv_quantity+? WHERE item_id=?");
            // stat items
//            cs = conn.prepareCall("{ call sklad.itemTracking(?,?,?) }"); // categoryID, advertHost, items_sum

            for(ItemsInBasket item : basket.getContents()) {

                us.setInt(1, basket.getOrderID());
                us.setInt(2, item.getItemID());
                us.setInt(3, item.getTotalQuantity());
                us.setDouble(4, item.getSellPrice());
                us.setString(5, item.getWarrantyDesc());
                us.setDouble(6, item.getWarrantyTotal());
                us.addBatch();

                us2.setInt(1, item.getTotalQuantity());
                us2.setInt(2, item.getTotalQuantity());
                us2.setInt(3, item.getItemID());
                us2.addBatch();

            }
            us.executeBatch();
            us.close();
            us2.executeBatch();
            us2.close();

            // for statistic
            cs = conn.prepareCall("{ call sklad.setDomainStat(?,0,1,?) }"); // advertHost, hitCount, orderCount, orderTotal
//            cs.setString(1, basket.getClickstream().getAdvertSite());
            cs.setInt(1, 1); // 1 = compuvest
            cs.setDouble(2, basket.getGrandTotal());
            cs.executeUpdate();
            cs.close();
        }
        catch(SQLException e) {
            log.error("makeOrder() orderNo=" + basket.getOrderNumber() + "\n" + e);
        }

        return basket.getOrderNumber();
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="setOrderStatus()">

    public void setOrderStatus(String orderNo, String authStatus) {

        try(Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement("UPDATE sklad.orders SET auth_status=? WHERE orderNo=?"))
        {
            us.setString(1, authStatus);
            us.setString(2, orderNo);

            us.executeUpdate();
        }
        catch(SQLException e) {
            log.error("setOrderStatus(): " + e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="setOrderPrechargeStatus()">

    public void setOrderPrechargeStatus(String orderNo, String status) {

        try(Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement("UPDATE sklad.orders SET auth_status=? WHERE orderNo=?"))
        {
            us.setString(1, status);
            us.setString(2, orderNo);

            us.executeUpdate();
        }
        catch(SQLException e) {
            log.error("setOrderPailyStatus(): " + e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="setBasket()">

    public void setBasket(Basket basket, String sessionID) {

        StringBuilder itemids = new StringBuilder(200);
        for(ItemsInBasket item : basket.getContents()) {
            itemids.append(item.getItemID()).append(",");
        }
        itemids.deleteCharAt(itemids.length()-1).toString(); // delete last ','

        String its = itemids.toString();
        its = its.length() > 100 ? its.substring(0, 99) : its;
        sessionID = sessionID.length() > 19 ? sessionID.substring(0, 19) : sessionID;

        try(Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement("REPLACE LOW_PRIORITY sklad.basket_session SET sessionID=?,items_id=?"))
        {
            us.setString(1, sessionID);
            us.setString(2, its);

            us.executeUpdate();
        }
        catch(SQLException e) {
            log.error("setBasket(): " + e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getBasket()">

    public Basket getBasket(String sessionID) {

        Basket basket = new Basket();

        try(Connection conn = ds.getConnection()) {
            String itemsid = "";

            PreparedStatement us = conn.prepareStatement("SELECT items_id FROM sklad.basket_session WHERE sessionID=?");
            us.setString(1, sessionID);

            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                itemsid = rs.getString("items_id");
            }
            rs.close();
            us.close();

            if(itemsid.length() != 0) {
                us = conn.prepareStatement(
                    "SELECT item_id,name,hot_price,(stock_quantity-reserv_quantity) AS quantity," +
                    "weight,IF(stock='Y',true,false) stock,RIGHT(accwareCatID,12) accwareCode,mfr_number,cs.description conditions," +
                    "warrantyMask,warrantyPrice1,warrantyPrice2,warrantyPrice3 " +
                    "FROM sklad.items i LEFT JOIN sklad.conditions cs ON LEFT(RIGHT(accwareCatID,2),1)=cs.id " +
                    "WHERE active='Y' AND item_id IN(" + itemsid + ") ");

                rs = us.executeQuery();
                while(rs.next()) {
                    Item item = new Item();

                    item.setItemID(rs.getInt("item_id"));
                    item.setItemName(rs.getString("name"));
                    item.setPrice(rs.getDouble("hot_price"));
                    item.setQuantity(rs.getInt("quantity"));
                    item.setWeight(rs.getDouble("weight"));
                    item.setStock(rs.getBoolean("stock"));
                    item.setAccwareCode(rs.getString("accwareCode"));
                    item.setMfrNumber(rs.getString("mfr_number"));
                    item.setCondition(rs.getString("conditions"));
                    item.setWarrantyMask(rs.getInt("warrantyMask"));
                    item.setWarrantyPrice1(rs.getDouble("warrantyPrice1"));
                    item.setWarrantyPrice2(rs.getDouble("warrantyPrice2"));
                    item.setWarrantyPrice3(rs.getDouble("warrantyPrice3"));

                    basket.addItem(new ItemsInBasket(item, false)); // (item,resseler)
                }
                rs.close();
                us.close();
            }
        }
        catch(SQLException e) {
            log.error("getBasket(): " + e);
        }

        return basket;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getShippingChartUS()">

    public List<ShipRate> getShippingChartUS(String zipCode, double weight) {

        List<ShipRate> shipList = new ArrayList<>(7);

        try { Integer.parseInt(zipCode); }
        catch(NumberFormatException ignore) {
            return shipList;
        }

        // if weight > 150lbs all next lbs + $1.45
        double shipAddPrice = 0d;
        if(weight > 150d) {
            shipAddPrice = (weight - 150d) * 1.45;
            weight = 150d;
        }

        try(Connection conn = ds.getConnection()) {
            StringBuilder sql = new StringBuilder(200);
            List<ShipRate> dayList = new ArrayList<>();

            PreparedStatement us = conn.prepareStatement(
                "SELECT ground,3day,2day,2day_am,1day_saver,1day,1day_am FROM sklad.upszone WHERE LEFT(?,3)>=zip_from AND LEFT(?,3)<=zip_to");
            us.setString(1, zipCode);
            us.setString(2, zipCode);

            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                if(rs.getString("ground") != null) {
                    sql.append(rs.getString("ground")).append(",");
                    dayList.add(new ShipRate("fdg", "Fedex Ground", rs.getString("ground")));

                    sql.append(rs.getString("ground")).append(",");
                    dayList.add(new ShipRate("usps", "USPS", rs.getString("ground")));
                }
                if(rs.getString("3day") != null) {
                    sql.append(rs.getString("3day")).append(",");
                    dayList.add(new ShipRate("fdx3d", "Fedex 3day Select", rs.getString("3day")));
                }
                if(rs.getString("2day") != null) {
                    sql.append(rs.getString("2day")).append(",");
                    dayList.add(new ShipRate("fdx2d", "Fedex Second Day", rs.getString("2day")));
                }
//              if(rs.getString("2day_am") != null) {
//                  sql.append(rs.getString("2day_am")).append(",");
//                  dayList.add(new ShipRate("2day_am", "Fedex Second Day Air A.M.", rs.getString("2day_am")));
//              }
                if(rs.getString("1day_saver") != null) {
                    sql.append(rs.getString("1day_saver")).append(",");
                    dayList.add(new ShipRate("fdxso", "Fedex Next Day Saver", rs.getString("1day_saver")));
                }
                if(rs.getString("1day") != null) {
                    sql.append(rs.getString("1day")).append(",");
                    dayList.add(new ShipRate("fdxpo", "Fedex Next Day Air", rs.getString("1day")));
                }
                if(rs.getString("1day_am") != null) {
                    sql.append(rs.getString("1day_am")).append(",");
                    dayList.add(new ShipRate("fdxfo", "Fedex Next Day Air Early A.M.", rs.getString("1day_am")));
                }
            }
            rs.close();
            us.close();

            if(sql.length() != 0) {
                sql.deleteCharAt(sql.length()-1); // delete ','

                us = conn.prepareStatement("SELECT " + sql + " FROM sklad.upsrate WHERE weight=CEILING(?)");
                us.setDouble(1, weight);
                rs = us.executeQuery();
                if(rs.next()) {
                    for(ShipRate ship : dayList) {
                        ship.setShipPrice(rs.getDouble(ship.getDay()), shipAddPrice);
                        shipList.add(ship);
                    }
                }
                rs.close();
                us.close();
            }

/*            if(dayList.size() < 7) { // if not all UPS add USPS
                us = conn.prepareStatement("SELECT z0 FROM sklad.uspsrate WHERE weight=CEILING(?)");
                us.setDouble(1, weight);

                rs = us.executeQuery();
                if(rs.next()) {
                    shipList.add(new ShipRate("usps", "USPS Express (3-8 Days)", rs.getDouble("z0")));
                }
                rs.close();
                us.close();
            }
*/
        }
        catch(SQLException e) {
            log.error("getShippingChartUS(): " + e);
        }

        return shipList.isEmpty() ? getDefaultWeight() : shipList;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getShippingChart()">

    public List<ShipRate> getShippingChart(String countryCode, double weight) {

        if(weight > 150d) {
            return getDefaultWeight();
        }

        List<ShipRate> shipList = new ArrayList<>(2);

        try(Connection conn = ds.getConnection()) {
            String zone = "";

            PreparedStatement us = conn.prepareStatement("SELECT zoneID FROM sklad.uspszone WHERE countryID=?");
            us.setString(1, countryCode);

            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                zone = rs.getString("zoneID");
            }
            rs.close();
            us.close();

            us = conn.prepareStatement("SELECT " + zone + " zone FROM sklad.uspsrate WHERE weight=CEILING(?)");
            us.setDouble(1, weight);

            rs = us.executeQuery();
            if(rs.next()) {
                shipList.add(new ShipRate("upsxi", "USPS International", rs.getDouble("zone")));
                shipList.add(new ShipRate("fdx2i", "Fedex Express (2-3 Days)", rs.getDouble("zone") * 2.5)); // international UPS
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.error("getShippingChart(): " + e);
        }

        return shipList.isEmpty() ? getDefaultWeight() : shipList;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getDefaultWeight()">

    private List<ShipRate> getDefaultWeight() {

        List<ShipRate> shipList = new ArrayList<>(1);

        shipList.add(new ShipRate("wen", "Weight Exceeded Normal", 0d));

        return shipList;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="loadWebStatistics()">

    final static String INFO_HOSTS =
        "SELECT IFNULL(ad.name,'deleted') host,SUM(sd.hits) hits,SUM(sd.orders) orders,SUM(sd.amount) amount " +
        "FROM sklad.statDomain sd " +
        "LEFT JOIN sklad.advert ad USING(advert_id) " +
        "WHERE sd.date=CURRENT_DATE() " +
        "GROUP BY sd.advert_id";
/*    final static String INFO_NOT_FILL_FORM =
        "SELECT count(*) cnt " +
        "FROM sklad.customer " +
        "WHERE joined BETWEEN CONCAT(CURRENT_DATE(),' 00:00:00') AND CONCAT(CURRENT_DATE(),' 23:59:59') " +
        "AND billingFirstName='' AND type='C'";
    final static String INFO_YES_FILL_FORM =
        "SELECT count(*) cnt " +
        "FROM sklad.customer " +
        "WHERE joined BETWEEN CONCAT(CURRENT_DATE(),' 00:00:00') AND CONCAT(CURRENT_DATE(),' 23:59:59') " +
        "AND billingFirstName<>'' AND (last_order_no='' OR last_order_no='0') AND type='C'";
    final static String INFO_RESELLER =
        "SELECT count(*) cnt " +
        "FROM sklad.customer " +
        "WHERE joined BETWEEN CONCAT(CURRENT_DATE(),' 00:00:00') AND CONCAT(CURRENT_DATE(),' 23:59:59') " +
        "AND billingFirstName<>'' AND (last_order_no='' OR last_order_no='0') AND type='C' AND reseller_code<>''";
*/

    public StatMap<String,Integer> loadWebStatistics() {

        StatMap<String,Integer> webInfo = new StatMap();
        webInfo.init(StatMap.ACTIVE); // init active session

        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(
                    "SELECT count(*) todayNewCust " +
                    "FROM sklad.customer " +
                    "WHERE joined BETWEEN CONCAT(CURRENT_DATE(),' ','00:00:00') AND CONCAT(CURRENT_DATE(),' ','23:59:59')");
            ResultSet rs = us.executeQuery();
            if (rs.next()) {
                webInfo.put(StatMap.TODAY_NEW_CUSTOMER, rs.getInt("todayNewCust")); // new customer
            }
            rs.close();
            us.close();

            us = conn.prepareStatement(
                    "SELECT count(*) cnt " +
                    "FROM sklad.customer " +
                    "WHERE joined BETWEEN CONCAT(CURRENT_DATE(),' 00:00:00') AND CONCAT(CURRENT_DATE(),' 23:59:59') " +
                    "AND billingFirstName='' AND type='C'");
            rs = us.executeQuery();
            if (rs.next()) {
                webInfo.put(StatMap.NOT_FILLED_FORM, rs.getInt("cnt")); // drop website not filled address (form)
            }
            rs.close();
            us.close();

            us = conn.prepareStatement(
                    "SELECT count(*) cnt " +
                    "FROM sklad.customer " +
                    "WHERE joined BETWEEN CONCAT(CURRENT_DATE(),' 00:00:00') AND CONCAT(CURRENT_DATE(),' 23:59:59') " +
                    "AND billingFirstName<>'' AND (last_order_no='' OR last_order_no='0') AND type='C'");
            rs = us.executeQuery();
            if (rs.next()) {
                webInfo.put(StatMap.YES_FILLED_FORM, rs.getInt("cnt")); // drop website but filled address (form)
            }
            rs.close();
            us.close();

            us = conn.prepareStatement(
                    "SELECT count(*) cnt " +
                    "FROM sklad.customer " +
                    "WHERE joined BETWEEN CONCAT(CURRENT_DATE(),' 00:00:00') AND CONCAT(CURRENT_DATE(),' 23:59:59') " +
                    "AND billingFirstName<>'' AND (last_order_no='' OR last_order_no='0') AND type='C' AND reseller_code<>''");
            rs = us.executeQuery();
            if (rs.next()) {
                webInfo.put(StatMap.RESELLER, rs.getInt("cnt")); // registered reseller
            }
            rs.close();
            us.close();

            us = conn.prepareStatement("SELECT count(*) totalCust FROM sklad.customer");
            rs = us.executeQuery();
            if (rs.next()) {
                webInfo.put(StatMap.TOTAL_CUSTOMER, rs.getInt("totalCust")); // total users
            }
            rs.close();
            us.close();

            us = conn.prepareStatement(
                    "SELECT count(*) todayOrders " +
                    "FROM sklad.orders " +
                    "WHERE order_date BETWEEN CONCAT(CURRENT_DATE(),' ','00:00:00') AND CONCAT(CURRENT_DATE(),' ','23:59:59')");
            rs = us.executeQuery();
            if (rs.next()) {
                webInfo.put(StatMap.TODAY_ORDERS, rs.getInt("todayOrders")); // today orders
            }
            rs.close();
            us.close();

            us = conn.prepareStatement("SELECT count(*) totalOrders FROM sklad.orders");
            rs = us.executeQuery();
            if (rs.next()) {
                webInfo.put(StatMap.TOTAL_ORDERS, rs.getInt("totalOrders")); // total orders
            }
            rs.close();
            us.close();

            us = conn.prepareStatement("SELECT SUM(hits) todayVisit FROM sklad.statDomain WHERE date=CURRENT_DATE() GROUP BY date");
            rs = us.executeQuery();
            if (rs.next()) {
                webInfo.put(StatMap.TODAY_VISIT, rs.getInt("todayVisit")); // today visit
            }
            rs.close();
            us.close();

            us = conn.prepareStatement("SELECT SUM(hits) totalVisit FROM sklad.statDomain");
            rs = us.executeQuery();
            if (rs.next()) {
                webInfo.put(StatMap.TOTAL_VISIT, rs.getInt("totalVisit")); // total visit
            }
            rs.close();
            us.close();

            webInfo.put(StatMap.TODAY_OLD_CUSTOMER,
                    (Integer) webInfo.get(StatMap.TODAY_ORDERS)
                    - ((Integer) webInfo.get(StatMap.TODAY_NEW_CUSTOMER) - (Integer) webInfo.get(StatMap.YES_FILLED_FORM)
                    - (Integer) webInfo.get(StatMap.NOT_FILLED_FORM) - (Integer) webInfo.get(StatMap.RESELLER))); // old customer

            String host = rs.getString("host");
            us = conn.prepareStatement(INFO_HOSTS);
            rs = us.executeQuery();
            for (int idx = 0; rs.next(); idx++) {
                webInfo.put(host + StatMap.VISITS, rs.getInt("hits"));
                webInfo.put(host + StatMap.ORDERS, rs.getInt("orders"));
                webInfo.put(host + StatMap.AMOUNT, rs.getInt("amount"));
            }
            rs.close();
            us.close();
        }
        catch (SQLException e) {
            log.error("loadWebStatistics(): " + e);
        }

        return webInfo;
    }
// </editor-fold>

}
